2

I'm trying to migrate a Redshift SQL script to Snowflake and trying to emulate week number on Snowflake. Below is the sample Redshift code:

select  cast(to_char('2020-01-06'::date, 'WW') as int)  as week,
        cast(to_char('2020-01-06'::date, 'IW') as int)  as iso_week,
        '2020-01-06'::date;

The closest functions I found on Snowflake was like this:

select cast(WEEKOFYEAR('2020-01-06'::date) as int)  as week,
       cast(WEEKISO('2020-01-06'::date) as int)     as iso_week,
       '2020-01-06'::date;

iso_week fields are matching, however week doesn't [Redshift shows 1, Snowflake shows 2]. Is there any function that emulates Redshift's behavior?

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
Majid Azimi
  • 5,575
  • 13
  • 64
  • 113

2 Answers2

1

This is the definition of WW on Redshift

WW: Week number of year (1–53; the first week starts on the first day of the year.)

Then the equivalent is to get the "day of year", and divide by 7 to count the number of weeks. We will also need some +/- ones to account for base 0:

select 1+floor((dayofyear('2020-01-06'::date)-1)/7)
-- 1

In UDF form:

create or replace function weeknumber_ww(x date) 
returns int
as $$
    1+floor((dayofyear(x)-1)/7)
$$
;

select weeknumber_ww('2020-01-06');
Felipe Hoffa
  • 54,922
  • 16
  • 151
  • 325
0

It is depenedent on parameter WEEK_OF_YEAR_POLICY

Specifies how the weeks in a given year are computed.

0: The semantics used are equivalent to the ISO semantics, in which a week belongs to a given year if at least 4 days of that week are in that year.

1: January 1 is included in the first week of the year and December 31 is included in the last week of the year.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • Setting this variable to 0 or 1 doesn't change behavior on Snowflake. It always returns 2. Even setting `WEEK_START` doesn't change behavior. – Majid Azimi Aug 19 '22 at 15:42
  • @MajidAzimi in both instances the resulting comparison is true which is why there is no difference. jan 1 2020 is a wed, there are 4 days in the week . You will have to change the week start. `alter session set week_start = 2;` – Sorceri Aug 19 '22 at 22:46