Convert date to ISO week date

How to convert dates to ISO week date in Impala SQL?

For example 2019-12-30 in the ISO week date calendar would be written as 2020-W01-1 or 2020W011

ANSWER:

Marked Gordon Linoff answer as correct, as it solves the essential part of the question, the deducing of the year part of ISO week date.

For the week part of the ISO week date there is a ready function, and the day part of the ISO week date can be easily converted from Sunday starting week to Monday starting week.

The query below contains all week dates from Monday to Sunday:

select datecol, 
       concat(cast(iso_year as string),'-W',lpad(cast(iso_week as string),2,'0'),'-',cast(iso_day as string)) as iso_Year_week_date_long,
       concat(cast(iso_year as string),'W',lpad(cast(iso_week as string),2,'0'),cast(iso_day as string)) as iso_Year_week_date_short
  from (
SELECT datecol, 
       (case when weekofyear(datecol) = 1 and
                  date_part('year',datecol) <> date_part('year',adddate(datecol,+7))
             then date_part('year',datecol) + 1
             when weekofyear(datecol) in (52, 53) and
                  date_part('year',datecol) <> date_part('year',adddate(datecol,-7))
             then date_part('year',datecol) - 1
             else date_part('year',datecol)
         end) as iso_year,
       weekofyear(datecol) as iso_week,
       1+mod(dayofweek(datecol)+5,7) as iso_day
  from (
  select '2021-12-31' as datecol union
  select '2020-12-31' as datecol union
  select '2019-12-31' as datecol union
  select '2018-12-31' as datecol union
  select '2017-12-31' as datecol union
  select '2016-12-31' as datecol union
  select '2015-12-31' as datecol union
  select '2014-12-31' as datecol union
  select '2013-12-31' as datecol union
  select '2012-12-31' as datecol union
  select '2022-01-01' as datecol union
  select '2021-01-01' as datecol union
  select '2020-01-01' as datecol union
  select '2019-01-01' as datecol union
  select '2018-01-01' as datecol union
  select '2017-01-01' as datecol union
  select '2016-01-01' as datecol union
  select '2015-01-01' as datecol union
  select '2014-01-01' as datecol union
  select '2013-01-01' as datecol
 ) as t1
  ) as t2
order by datecol;

and shows how January 1st belongs to

the new year, if January 1st is 1st, 2nd, 3rd or 4th day of the week, i.e., if there are at least 4 new year days in the week containing January 1st
the old year, if January 1st is 5th, 6th or 7th day of the week, i.e., if there are 3 or less new year days in the week containing January 1st

datecol   |iso_year_week_date_long|iso_year_week_date_short|
----------|-----------------------|------------------------|
2014-12-31|2015-W01-3             |2015W013                |
2015-01-01|2015-W01-4             |2015W014                |
2015-12-31|2015-W53-4             |2015W534                |
2016-01-01|2015-W53-5             |2015W535                |
2016-12-31|2016-W52-6             |2016W526                |
2017-01-01|2016-W52-7             |2016W527                |
2017-12-31|2017-W52-7             |2017W527                |
2018-01-01|2018-W01-1             |2018W011                |
2018-12-31|2019-W01-1             |2019W011                |
2019-01-01|2019-W01-2             |2019W012                |
2019-12-31|2020-W01-2             |2020W012                |
2020-01-01|2020-W01-3             |2020W013                |
2020-12-31|2020-W53-4             |2020W534                |
2021-01-01|2020-W53-5             |2020W535                |

Answer

I think Impala returns the iso week for date_part() and extract() — based on your previous question. There is no documentation to this effect.

If so, you can use conditional logic:

select (case when date_part(week, datecol) = 1 and
                  date_part(year, datecol) <> date_part(year, datecol + interval 1 week)
             then date_part(year, datecol) + 1
             when date_part(week, datecol) in (52, 53) and
                  date_part(year, datecol) <> date_part(year, datecol - interval 1 week)
             then date_part(year, datecol) - 1
             else date_part(year, datecol)
         end) as iso_year,
        date_part(week, datecol) as iso_week

Otherwise, you can get the first day of the iso year using:

select (case when to_char('DD', date_trunc(year, datecol), 'DD') in ('THU', 'FRI', 'SAT', 'SUN')
             then next_day(date_trunc(year, date_trunc(year, datecol)), 'Monday')
             else next_day(date_trunc(year, date_trunc(year, datecol)), 'Monday') - interval 7 day
         end) as iso_year_start

You can then calculate the iso week from the start of the iso year using arithmetic.

Advertisement

Answer