Converting YYYYMMDD string to date in standard SQL / BigQuery

Question

I have this column called 'Date' that contains dates formatted this way: '20150101'.

I tried using sql substring, but when I run the query using 'date' function in sql, it doesn't work on the date format I have.

Here is the query I made:

SELECT (DATE ((SUBSTR(JOUR, 1, 4), 
               SUBSTR(JOUR, 5, 2), 
               SUBSTR(JOUR, 7, 2)))) As date 
FROM TABLE

Any idea? I couldn't find anything similar to this date format! I found one that uses the convert function but it's not in StandardSQL or BigQuery

it's a string that looks like AAAAMMDD and I'm trying to convert it to a date that look like: AAAA-MM-DD — helloworld

dani herrera dani herrera · Accepted Answer · 2019-02-18T14:43:00

What about PARSE_DATE ?

SELECT PARSE_DATE("%Y%m%d", "20190101") as parsed;

For your table:

SELECT PARSE_DATE ("%Y%m%d", JOUR) As mydatecolumn from TABLE