Question 95587 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

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

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?

Tags:

2

Answers


  1. 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.

    Login or Signup to reply.
  2. 0

    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');
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search