LAST_DAY('WEEK') ignores WEEK_OF_YEAR_POLICY session parameter

Question

I realized that on our Time table we had days of December as part of 1st week of 2022. Even after setting WEEK_OF_YEAR_POLICY = 1 variable to, I still get that the last day for week 53 of 2021 is 01-01-2022:

    -- Jan 1st always starts the 1st week; Week starts on Sunday
    ALTER SESSION SET week_of_year_policy=1, week_start=7;
 
   WITH cte_my_date AS (
    SELECT 
        Dateadd(day, Seq4(), '2021-01-01 00:00:00') AS MY_DATE
    FROM
        TABLE(Generator(rowcount => 365))
    )
    
    select my_date, week(my_date), Last_day(my_date, 'WEEK') from cte_my_date

Is there an out-of-the-box way to retrieve 31-Dec-2021 as last day of the week?

user_132438 · Answer

You could use least() to enforce the restriction of last day of year vs last day of week:

WITH cte_my_date AS (
SELECT 
    Dateadd(day, Seq4(), '2021-12-16 00:00:00') AS MY_DATE
    FROM TABLE(Generator(rowcount => 15))
)

select my_date, week(my_date)
    , least(last_day(my_date, 'WEEK'), last_day(my_date, 'YEAR')) last
from cte_my_date
;

LAST_DAY('WEEK') ignores WEEK_OF_YEAR_POLICY session parameter

1 Answers