How to calculate iso_week_start_date and iso_week_end_date for date dimension?

Question

As part of calendar dimension, I need to populate iso_week_start_date, iso_week_end_date and iso_week_number. I have been able to build the calendar dimension except these fields. Thanks.

Answer 1

Snowflake offers DATE_TRUNC(WEEK, ..) which lets you get the first day of the ISO week. Then adding 6 days gives you the last day. And there's also DATE_EXTRACT(WEEK, ..) (or simply WEEK(..))

For example:

select extract(week, '2017-10-10'::date), date_trunc(week, '2017-10-10'::date), dateadd(day, 6, date_trunc(week, '2017-10-10'::date));
-----------------------------------+--------------------------------------+-------------------------------------------------------+
 EXTRACT(WEEK, '2017-10-10'::DATE) | DATE_TRUNC(WEEK, '2017-10-10'::DATE) | DATEADD(DAY, 6, DATE_TRUNC(WEEK, '2017-10-10'::DATE)) |
-----------------------------------+--------------------------------------+-------------------------------------------------------+
 41                                | 2017-10-09                           | 2017-10-15                                            |
-----------------------------------+--------------------------------------+-------------------------------------------------------+

Remember that ISO weeks have a bit weird semantics, see the documentation for more detail.