Is there a way to convert ISO 8601 to date format in BigQuery?

I have input from Amazon Alexa in the format of ISO 8601 and was wandering if I needed to do a whole bunch of string substrings & transforms to make it into a BigQuery Timestamp format, or is there some function that does it?

I also understand that it is hard to turn 2015-W49 into a date, but thought I would ask.

references: https://developer.amazon.com/public/solutions/alexa/alexa-skills-kit/docs/built-in-intent-ref/slot-type-reference#date https://en.wikipedia.org/wiki/ISO_8601#Dates https://code.google.com/p/google-bigquery/issues/detail?id=208

Solution

I would expect below result of such conversion, which is first day of the respective week, which should be respectively :

Week Date in ISO 8601   First Day of the week    
2015-W01                2014-12-28   
2015-W02                2015-01-04   
2015-W49                2015-11-29

You can verify above at http://www.enpicbcmed.eu/calendar/2015-W01 for example

I think below returns correct result

#standardSQL
WITH data AS (
  SELECT '2015-W01' AS dt UNION ALL
  SELECT '2015-W02' AS dt UNION ALL
  SELECT '2015-W49' AS dt  
)
SELECT 
  dt, 
  DATE_ADD(PARSE_DATE("%Y-W%W", dt), 
           INTERVAL 7 * CAST(SUBSTR(dt,-2,2) AS INT64) - 6 - 
           EXTRACT (DAYOFWEEK FROM PARSE_DATE("%Y-W%W", dt)) DAY
  ) as d
FROM data
ORDER BY dt