'How to get week number of month for any given date on snowflake SQL

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.

sqlsnowflake-cloud-data-platformweek-number


Solution 1:[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;

Solution 2:[2]

You can try

 WEEKOFYEAR(dt) - WEEKOFYEAR(CAST(date_trunc('MONTH',dt) AS DATE))+1 AS  "WEEK_OF_MONTH"

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 PM 77-1

Related Questions