How to construct date from (year, weekOfYear, dayOfWeek) in Snowflake?

Question

I have a situation where using Snowflake, I need to construct a date from these parts (year, weekOfYear, dayOfWeek). ie (2022, 13th week, 3 for Weds) would equate to today's date 2022-03-30.

While this is possible in say Pandas, Snowflake's day_from_parts() does not seem to support this functionality. Is there some other method to construct a date from these 3 pieces of information?

For context, I am ultimately trying to create flags in my dimDate for 'weekToDate' and 'weekToDatePrevYear' (the prev year comparison is not actually the same date, but instead is Mon - today's dayOfWeek of the same weekOfYear, prev year). The flags must also account for weeks that span across two year's, and leap years with 53 weeks (in which case it will be compared to the 52nd week of prev year). The date I need to construct is the previous year's comparisonDate to today, from parts:

(year(current_date())-1, min(weekOfYear(current_date()), 52), dayOfWeek(current_date()))

Thanks in advance for any suggestions!

We have [TIMESTAMP_FROM_PARTS](https://docs.snowflake.com/en/sql-reference/functions/timestamp_from_parts.html#timestamp-from-parts) that can be converted later to a date but it doesn't support week as argument, just month, so you might need some work to get the month rather than week. — Sergiu, Mar 30 '22 at 15:25

score 0 · Answer 1 · answered Mar 30 '22 at 15:25

Remember to set following parameter to ensure that, the first week of the year is the week that contains January 1st of that year.

ALTER SESSION SET WEEK_OF_YEAR_POLICY = 1;
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+

Generate the calender for whole year. Used sub-query from @Simeon Pilgrim

select * from
(
 select row_number() over (order by null)-1 as rn
        ,dateadd('day', rn, '2022-01-01'::date) as date_dt,WEEKOFYEAR(dateadd('day', rn, '2022-01-01'::date)
) as woy, DAYOFWEEK(dateadd('day', rn, '2022-01-01'::date)) dow
  from table(generator(rowcount=>365))
 )
 limit 10;
+----+------------+-----+-----+
| RN | DATE_DT    | WOY | DOW |
|----+------------+-----+-----|
|  0 | 2022-01-01 |   1 |   6 |
|  1 | 2022-01-02 |   1 |   0 |
|  2 | 2022-01-03 |   2 |   1 |
|  3 | 2022-01-04 |   2 |   2 |
|  4 | 2022-01-05 |   2 |   3 |
|  5 | 2022-01-06 |   2 |   4 |
|  6 | 2022-01-07 |   2 |   5 |
|  7 | 2022-01-08 |   2 |   6 |
|  8 | 2022-01-09 |   2 |   0 |
|  9 | 2022-01-10 |   3 |   1 |
+----+------------+-----+-----+

Get the desired date based on day-of-week and week-of-year

select date_dt from
(
 select row_number() over (order by null)-1 as rn
        ,dateadd('day', rn, '2022-01-01'::date) as date_dt,WEEKOFYEAR(dateadd('day', rn, '2022-01-01'::date)
) as woy, DAYOFWEEK(dateadd('day', rn, '2022-01-01'::date)) dow
  from table(generator(rowcount=>365))
 )
 where woy=13 and dow=4;
+------------+
| DATE_DT    |
|------------|
| 2022-03-24 |
+------------+

Refer here for more information on extract date parts.

For this - "The flags must also account for weeks that span across two year's, and leap years with 53 weeks (in which case it will be compared to the 52nd week of prev year). " DONOT set WEEK_OF_YEAR_POLICY = 1; — Pankaj, Mar 30 '22 at 16:09
Thanks @Panjak! I'll need a little time to review and test this solution. However, I do not want Jan 1 to always be the 1st week of the year... I know this is the default in some databases, but I find that behavior bizarre. Mon (or Sun, depending on your preference) should always be the start of a new week. — EricB, Mar 30 '22 at 16:18
Yes, like I said, if you don't set WEEK_OF_YEAR_POLICY = 1 in that case Jan 1 will not be interpreted as 1st week. Like for this year it comes out as 52nd week. — Pankaj, Mar 30 '22 at 16:21

score 0 · Accepted Answer · answered Apr 02 '22 at 13:15

@Panjak I was unable to get your code working... this piece "Get the desired date based on day-of-week and week-of-year" just got me today's date, previous year.

However I was able find another solution using a CTE! This is my final view with the WTD and WTD_PREV_YR flags. Note, it also contains MTD, MTD_PREV_YR, YTD, YTD_PREV_YR.

create or replace view DIM_DATE as (
    with comparison_date as (
        select
            case
                when weekofyear(current_date()) = 53
                then 52
                else weekofyear(current_date())
            end as comp_week
            , date as comp_date
            from <TABLE>
            where year(comp_date) = year(current_date()) - 1
            and weekofyear(comp_date) = comp_week
            and dayofweek(comp_date) = dayofweek(current_date())
    )
    select distinct
        d.date
        , case
            when weekofyear(date) = weekofyear(current_date())
            and date - current_date() <= 0
            and date - current_date() >= -6
            then 1
            else 0
        end as WTD
        , case
            when weekofyear(date) = weekofyear(c.comp_date)
            and date - c.comp_date <= 0
            and date - c.comp_date >= -6
            then 1
            else 0
        end as WTD_PREV_YR
        , CASE
            WHEN MONTH(DATE) = MONTH(CURRENT_DATE())
            AND DAYOFYEAR(DATE) <= DAYOFYEAR(CURRENT_DATE())
            AND YEAR(DATE) = YEAR(CURRENT_DATE())
            THEN 1
            ELSE 0
        END AS MTD
        , CASE
            WHEN MONTH(DATE) = MONTH(CURRENT_DATE())
            AND DAYOFYEAR(DATE) <= DAYOFYEAR(CURRENT_DATE())
            AND YEAR(DATE) = YEAR(CURRENT_DATE()) - 1
            THEN 1
            ELSE 0
        END AS MTD_PREV_YR
        , CASE
            WHEN DAYOFYEAR(DATE) <= DAYOFYEAR(CURRENT_DATE())
            AND YEAR(DATE) = YEAR(CURRENT_DATE())
            THEN 1
            ELSE 0
        END AS YTD
        , CASE
            WHEN DAYOFYEAR(DATE) <= DAYOFYEAR(CURRENT_DATE())
            AND YEAR(DATE) = YEAR(CURRENT_DATE()) - 1
            THEN 1
            ELSE 0
        END AS YTD_PREV_YR
    from <TABLE> as d
    join comparison_date as c on 1=1
    order by date desc
);

How to construct date from (year, weekOfYear, dayOfWeek) in Snowflake?

2 Answers2

Get the desired date based on day-of-week and week-of-year