1

I'm trying to extract the week number of the month for any given date. If the month starts on a Saturday, it should be labelled 0. Thereafter, the weeks will be labelled 1, 2, 3, ...

For example, for Jan 2022, 1st Jan is Week 0, 2nd Jan is Week 1, 9th Jan is Week 2, 16 Jan is Week 3, 23 Jan is Week 4, and 30th Jan is Week 4.

I tried converting some MS SQL & TSQL codes from other Stack Overflow questions but it did not work on Snowflake.

I guess the alternative is to hardcode using Sunday dates but the code is far too long and has too many conditions. Hoping for a shorter, cleaner code using Snowflake's inbuilt SQL functions.

Adrian Mole
  • 49,934
  • 160
  • 51
  • 83
aphrodite
  • 11
  • 1
  • 2

2 Answers2

1

You have to deal with the WEEK_START and WEEK_OF_YEAR_POLICY parameters. The calculation should not be complicated, see my example (unfortunately you did not enter when your week start):

SHOW PARAMETERS LIKE 'WEEK%';
ALTER SESSION SET WEEK_OF_YEAR_POLICY=1, WEEK_START=7;

WITH sample_dates AS (
  SELECT DATEADD('DAY', SEQ4(), '2022-01-01 00:00:00'::timestamp) AS dt
    FROM TABLE(GENERATOR(ROWCOUNT => 365)) AS t
)
SELECT DATE(dt) AS "DATE"
     , DAYOFYEAR(dt) AS "DAY OF YEAR"
     , DAYOFMONTH(dt) AS "DAY OF MONTH"
     , DAYOFWEEK(dt) AS "DAY OF WEEK"
     , DAYNAME(dt) AS "DAY NAME"
     , WEEK(dt) AS "WEEK"
     , WEEKISO(dt) AS "WEEK ISO"
     , WEEKOFYEAR(dt) AS "WEEK OF YEAR"
     , IFF(DAYNAME(DATE_TRUNC("YEAR", dt)) = 'Sat', WEEK(dt) - 1, WEEK(dt)) AS "CALC_DATE"
     , IFF(DAYOFMONTH(dt) = 1 AND DAYNAME(dt) = 'Sat', 0, FLOOR((DAY(dt)+6.1)/7,0)) AS "WEEK OF MONTH"
  FROM sample_dates;
Michael Golos
  • 1,814
  • 1
  • 6
  • 16
  • Hi Michael, thanks for this. My week starts on Sunday. The output should be the week number of the month for the input date. e.g. for 2 Jan 2022, the output should be 2. For 12 Feb 2022, the output should be 2. – aphrodite Feb 12 '22 at 10:55
  • OK then see my query after modification, I added WEEK OF MONTH calculation. – Michael Golos Feb 12 '22 at 15:10
0

You can try

 WEEKOFYEAR(dt) - WEEKOFYEAR(CAST(date_trunc('MONTH',dt) AS DATE))+1 AS  "WEEK_OF_MONTH"
PM 77-1
  • 12,933
  • 21
  • 68
  • 111
Bipin Babu
  • 61
  • 5