Skip to content
Advertisement

Parse out Y-M-D from Y-M-D H-M-S UTC sql bigquery

I need to parse out '%Y%m%d' from the column in BigQuery. My data looks like this:

datetime_published
2000-09-25 13:28:15 UTC
2018-12-22 16:03:00 UTC
2018-05-04 03:05:00 UTC

I have tried the following:

SELECT PARSE_DATE('%Y%m%d', datetime_published) as date

The error message: No matching signature for function PARSE_DATE for argument types: STRING, TIMESTAMP. Supported signature: PARSE_DATE(STRING, STRING) Desired output: 2000-09-25

Advertisement

Answer

Why not just convert to a date?

select date(datetime)

Note: This works for both datetime and timestamp values. These are different in BigQuery. You have a timestamp column which you have called datetime — a bit of a misnomer.

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement