Convert calendar week and year to date in ONE DATA


Is there any easy way to convert a calendar week and year in the following format to the date corresponding to the start of the calendar week (German format) ?


2022/KW1 → 2022-03-01
2022/KW2 → 2022-10-01

The answer depends on the definition of the first week of the year. Assuming the first week of a year starts with the first Monday of the year you can do the following:
First Query processor:

TO_DATE(CONCAT(year, '-01-01'), 'yyyy-MM-dd') AS first_day

Next Query processor:

DATE_ADD(first_day, MOD((9 - DAYOFWEEK(first_day)),7)) AS first_monday

Next Query processor:

TIMESTAMP(DATE_ADD(first_monday, INT((week-1)*7))) AS date

Of course, you can combine these in one statement, but this might be more clear to follow.


Exactly what I needed, thank you!! :smile:

In case anybody needs it with thursday as first day of week:

MOD((12 - DAYOFWEEK(first_day)),7)) AS first_thursday

Thank you Katharina

Just for the sake of completeness: If you would like to do the opposite, you cannot use date_format(date,‘YYYY-ww’) any more in Spark 3. Unfortuantelly you still get a return value, but it is not always correct (especially at the year changes).

CONCAT(extract(YEAROFWEEK FROM DATE '2022-01-01'), '-', extract(WEEK FROM DATE '2022-01-01')) as calendar_week
gives you the ISO 8601 calendar week (see Spark documentation for more details)