what week of year is December 1, 2020?
dataders opened this issue · 9 comments
Feeling like a crazy person trying to resolve the newly broken test in dbt-msft/tsql-utils#45.
- dbt_utils.expression_is_true:
expression: "week_of_year = {{ dbt_date.week_of_year('date_day') }}" What should the week of year be for 2020-12-01? the default__get_test_dates() says is it 48, but TSQL says otherwise despite datepart providing a week and isoweek see docs
SELECT
datepart(week, CAST('2020-12-01' as date)) as week_of_year,
datepart(iso_week, CAST('2020-12-01' as date)) as iso_week_of_year| week_of_year | iso_week_of_year |
|---|---|
| 49 | 49 |
interestingly, in @b-per's fork's week_of_year() he subtracts a day, ostensibly just to get the test to pass...
{%- macro sqlserver__week_of_year(date) -%}
cast({{ dbt_date.date_part('week', dbt_utils.dateadd('day',-1,date)) }} as {{ dbt_utils.type_int() }})
{%- endmacro %}Welcome to the worst part of data, dates!
Does SQL Server maybe always provide ISO weeks? Snowflake did that to me, hence
@clausherther can you show me a week number calendar that puts December 1, 2020 in the 48th week of the year?
update
as I see now that week 48 is if we use a US-based calendar where the week starts on Sunday?
Yeah BQ for sure thinks so for Sunday weeks:
Yes, the better way to think of week is "Sunday Week" vs ISO ("Monday Week"). That's not super correct, but a good proxy that I use.
But:
I'm pretty sure if you checked 3 different electronic calendars (e.g. the one on my Mac etc) you'd get 3 different results when you switch from Sunday to Monday week.
ok, coming back up for air after a stack overflow deepdive. you're totally right. I guess we need a sqlserver_get_test_dates() macro after all....
The SQL server integrated `DATEPART() function does not necessarily do The Right Thing. SQL Server assumes day 1 of week 1 would be January 1, for many applications that's wrong.
https://stackoverflow.com/questions/348880/getting-week-number-off-a-date-in-ms-sql-server-2005
Good thing we made test_dates a dispatched macro!
interestingly, in @b-per's fork's
week_of_year()he subtracts a day, ostensibly just to get the test to pass...{%- macro sqlserver__week_of_year(date) -%} cast({{ dbt_date.date_part('week', dbt_utils.dateadd('day',-1,date)) }} as {{ dbt_utils.type_int() }}) {%- endmacro %}
Guilty ;-)
When I originally tinkered with dbt-date on T-SQL I was more focused on getting it to work to a stage so that I could use dbt-expectations but I didn't spend a huge amount of time dealing with the more complex cases.
Another way to deal with this is to simply not support non-ISO week of year on your platform. I'm thinking of doing this for Presto:
https://github.com/calogica/presto-utils/blob/main/macros/dbt_date/calendar_date/week_of_year.sql
We'll still want tests to pass, so I'm also providing specific test data for Presto, but at least we've warned users.