Convert day of year & year to date Big Query

I have some data which instead of having a date field, contains the day of the year as a number and the year as a number in different columns.

I have tried using big query functionality PARSE_DATE to achieve this by using PARSE_DATE("%Y %j", "2020 258") but this does not work. When checking the docs https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#parse_date %j is not supported with PARSE_DATE.

Does anyone know how I can get around this?

For reference, the following query is as close as I can get:

SELECT PARSE_DATE("%Y %j", "2020 258") which returns 2020-01-01

Answer

You can use a brute force method, by adding the number of days to the beginning of he year:

select date_add(parse_date('%Y', split(yyyyddd, ' ')[ordinal(1)]),
                interval cast(split(yyyyddd, ' ')[ordinal(2)] as int64) - 1 day
               )
from (select '2020 258' as yyyyddd) x;

PARSE_DATE() does not support all format elements. As the documentation (rather defensively) explains:

The format string fully supports most format elements except for %Q, %a, %A, %g, %G, %j, %u, %U, %V, %w, and %W.