How to replicate week number that was generated using Redshift's to

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?

CodePudding user response：

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.

CodePudding user response：

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');