ISO week to date in snowflake

user_4106934 0
Viewed 390

I have ISO week and year as yyyy|ww. how do I turn this to date in Snowflake? For example: 2021|13 should return 2021-03-29.

to_date('2021|13','yyyy|ww') is not working. It seems snowflake does not recognize 'ww' format.

snowflake-cloud-data-platform
2 Answers

You can create a UDF to do the job:

create or replace function isoweek_to_date(s string)
returns date
  as
  $$
    select DATEADD(
      DAY,
      WEEK * 7 
      - CASE 
          WHEN DAYOFWEEKISO(DATE_FROM_PARTS(YEAR, 1, 1)) < 5 
          THEN 7 ELSE 0 
         END
      + 1 
      - DAYOFWEEKISO(DATE_FROM_PARTS(YEAR, 1, 1)),
      DATE_FROM_PARTS(YEAR, 1, 1)
    )
    from (select regexp_substr(s, '[0-9]+', 1, 1) year, regexp_substr(s, '[0-9]+', 1, 2) week)
  $$
;

select isoweek_to_date('2021|13')
// 2021-03-29
;

Code taken from Hans' https://stackoverflow.com/a/58298264/132438.

user_1324380

I think the following is what you're looking for. Looks similar to Felipe/Hans' solution.

It adds 12 weeks onto 2021-01-01, gets the last day of that week with last_day and then adds 1 to get the first day of week 13.

select
    '2021|13' str_dt,
    dateadd('day', 1, last_day(dateadd('WEEK', SPLIT(str_dt, '|')[1]::int - 1, SPLIT(str_dt, '|')[0] || '-01-01'), 'week'))
user_32872500
Related