Presto how to find start date given week

I want to find start date from given ISO week (which can range from 1-53, Monday as starting day) and year using Presto SQL query.

i.e. year – 2020 and week – 2 should return 06/01/2020

Is there any inbuilt function for this ?

Table structure:

select year, week from table1; // returns year and week from table table 1

Answer

There’s no direct way for constructing a date from a year + week (there is an issue for this: https://github.com/trinodb/trino/issues/2287), but you can achieve what you want with the date_parse function.

For example:

WITH data (year, week) AS (VALUES (2020, 2))
SELECT CAST(date_parse(CAST(year AS varchar) || ':' || CAST(week AS varchar), '%x:%v') AS date)
FROM data

produces:

   _col0
------------
 2020-01-06
(1 row)