You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
I am trying to create a date dimension with Snowflake. I need the week to start on Sunday.
I finally figured out I needed to change the Snowflake "WEEK_START" setting to 7 (Sunday) to get week numbers to accurately reflect a Sunday start.
Now the issue is with Day of Week. With Snowflake "WEEK_START" set to 7, here is the output of dbt-date for iso/non-iso:
Sunday = 7 (DAY_OF_WEEK_ISO)
Sunday = 2 (DAY_OF_WEEK)
I need Sunday to be 1. Looking at the code in DAY_OF_WEEK i stumbled upon this line below that is adding + 1 (did Snowflake use to return 0?) -- this is causing Sunday to be 2 in DAY_OF_WEEK which is not correct.
If I run the snowflake functions directly for 01/02/2022, a Sunday, they are correct:
SELECT DAYOFWEEK(date'2022-01-02') = 1
SELECT DAYOFWEEKISO(date'2022-01-02') = 7
So my guess is that +1 in the DAYOFWEEK is not needed anymore.
The text was updated successfully, but these errors were encountered:
I am trying to create a date dimension with Snowflake. I need the week to start on Sunday.
I finally figured out I needed to change the Snowflake "WEEK_START" setting to 7 (Sunday) to get week numbers to accurately reflect a Sunday start.
Now the issue is with Day of Week. With Snowflake "WEEK_START" set to 7, here is the output of dbt-date for iso/non-iso:
Sunday = 7 (DAY_OF_WEEK_ISO)
Sunday = 2 (DAY_OF_WEEK)
I need Sunday to be 1. Looking at the code in DAY_OF_WEEK i stumbled upon this line below that is adding + 1 (did Snowflake use to return 0?) -- this is causing Sunday to be 2 in DAY_OF_WEEK which is not correct.
https://github.com/calogica/dbt-date/blob/df2f567efacd1e0074f86600812213f5d79c1b1b/macros/calendar_date/day_of_week.sql#L30C1-L30C62
If I run the snowflake functions directly for 01/02/2022, a Sunday, they are correct:
SELECT DAYOFWEEK(date'2022-01-02') = 1
SELECT DAYOFWEEKISO(date'2022-01-02') = 7
So my guess is that +1 in the DAYOFWEEK is not needed anymore.
The text was updated successfully, but these errors were encountered: