2

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.

Mohit
  • 33
  • 1
  • 4

1 Answers1

2

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.

Marcin Zukowski
  • 4,281
  • 1
  • 19
  • 28
  • Thanks Marcin. This is very helpful. Does this mean that Snowflake has native support of ISO dates ? Then how can I get regular week number (not ISO) from a particular date ? is there a different function for that ? There is one function I could find in SF documentation "dayofweek_iso ". Are you aware of any other such functions ? I couldn't find any such thing in documentation. – Mohit Oct 11 '17 at 10:35
  • Today, Snowflake provides mostly ISO-based week functions: WEEK returns ISO week for example. Also, Snowflake will soon provide additional week-related functionality. Hopefully this quarter. This should address your requirements. – Marcin Zukowski Oct 11 '17 at 17:25
  • Btw, if you can provide the exact kind of functionality you'd like to see, please add a comment with it, as detailed as possible. For example, do you want Dec 31st to always belong to the week of its year (e.g. 52 or 53), or can it belong to week 1 of the next year. – Marcin Zukowski Oct 11 '17 at 17:26
  • Thank you for the followup. Ask here is to have both regular as well as ISO date formats supported in Snowflake. Based on reporting type, we choose whether to use ISO format or regular one's. – Mohit Oct 12 '17 at 06:47