amazon-web-servicessnowflake-cloud-data-platformamazon-redshiftweek-numberto-char

How to replicate week number that was generated using Redshift's to_char on Snowflake?


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?


Solution

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