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, Feb 18 '19 at 14:42
Possible duplicate of [STRING to DATE in BIGQUERY](https://stackoverflow.com/questions/44564887/string-to-date-in-bigquery) — Sam Rockett, Feb 18 '19 at 15:13

score 19 · Accepted Answer · answered Feb 18 '19 at 14:43

19

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

answered Feb 18 '19 at 14:43

dani herrera

Any reason why this wouldn't work and produces this message: Failed to parse input string "12102021"? Any help here is most appreciated! – lcm Jun 08 '22 at 07:14
"12102021" using "%Y%m%d" is "1210-20-21" and raises error because 20 is not a valid moth. Change mask format to "%m%d%Y" or whatever according to format you are using. – dani herrera Jul 05 '23 at 14:23

1 Answers1