Hi,
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) ?
Ex:
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.
2 Likes
Exactly what I needed, thank you!! 
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).
Instead
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)