Q:

Extraxt date from ISO Week (201905) BigQuery

I need to extract date of Sunday from it's ISO Week number: i.e. 201905
It needs to be in #standardSQL as it's going to be scheduled with scripts that don't support legacy.
I tried adjust working formula from Google Sheets but can't figure it out.
The original formula from Google Sheets:
TO_TEXT (
(DATE(LEFT(Week_ISO,4),1,1)
- (WEEKDAY(DATE(LEFT(Week_ISO,4),1,1))-2)
+ (RIGHT(Week_ISO,2)-1)*7) 
+6)

A:

From my reading of the documentation, this should work:
PARSE_DATE('%G%V', isoyyyymm)

But it doesn't.
So, here is an alternative:
SELECT DATE_ADD(DATE_TRUNC(PARSE_DATE('%Y%m%d', CONCAT(substr(isoyyyyww, 1, 4), '0601')),
                           isoyear
                          ),
                INTERVAL CAST(substr(isoyyyyww, -2) as int64) WEEK
               )
FROM (SELECT '200506' as isoyyyyww);

The idea here is the following:

Convert your yyyyww format into a date in the middle of the year.
Truncate the date to the beginning of the ISO year.
Add back the appropriate number of weeks.

You can adapt this if your value is a number rather than a string.

