Value based Format

The value base format with the weekday name from its number (o being sunday).

select case cast(strftime('%w', date()) as integer)
           when 0 then 'Sunday'
           when 1 then 'Monday'
           when 2 then 'Tuesday'
           when 3 then 'Wednesday'
           when 4 then 'Thursday'
           when 5 then 'Friday'
           else 'Saturday' end as english_weekday

Conditional expression base case

The conditional based format

Example where Saturday and Sunday are marked as weekend and the other days as midweek

with day as (select cast(strftime('%w', date()) as integer) as weekday)
select case
           when day.weekday == 0 then 'Weekend'
           when day.weekday == 6 then 'Weekend'
           else 'MidWeek' end as weekpart
from day