UDF: Weekday

UDF: Weekday

Description

A UDF to extract the day of the week (e.g. “Monday”) from a given date.

CREATE OR REPLACE FUNCTION <DATASET>.weekday(dt DATE) RETURNS STRING AS
(
    case when extract(DAYOFWEEK from dt) = 1 then 'Sunday'
    when extract(DAYOFWEEK from dt) = 2 then 'Monday' 
    when extract(DAYOFWEEK from dt) = 3 then 'Tuesday'
    when extract(DAYOFWEEK from dt) = 4 then 'Wednesday'
    when extract(DAYOFWEEK from dt) = 5 then 'Thursday'
    when extract(DAYOFWEEK from dt) = 6 then 'Friday'
    when extract(DAYOFWEEK from dt) = 7 then 'Saturday'
end 
);

Example (optional)

    select dates from unnest(generate_date_array('2021-02-28', '2021-12-02', INTERVAL 3 month)) AS dates
)
select 
    dates,
    spotify.weekday(dates) weekday,
    extract(dayofweek from dates) wd
from data
dates weekday wd
2021-02-28 Sunday 1
2021-05-28 Friday 6
2021-08-28 Saturday 7
2021-11-28 Sunday 1