How to Parse values in column with different date strings in Standard SQL in BigQuery-CodePudding

i have a column in my table in bigquery which has different date strings, which are formatted as strings:

 -------------------
| date              |
 -------------------
|2021-12-31         |
 -------------------
|2021-12-25T15:45:00|
 -------------------
|2021-11-15         |
 -------------------
|2021-12-08T09:00:00|
 -------------------
|2021-12-31         |
 -------------------

i tried using

EXTRACT(DATE FROM PARSE_TIMESTAMP("%FT%T", "2021-12-08T15:45:00")) as date

but this does not work for the YYYY-MM-DD date string. I get the error message

Failed to parse input string "2021-12-31

And when i try

PARSE_DATE('%Y%m%d', LEFT("2021-12-21T15:45:00", 10)) AS date

i get the same error message for both string types of this column.

How can i format the strings in dates in YYYY-MM-DD form?

CodePudding user response：

Consider below simple approach

select *, date(date_string) as date_date
from your_table

if applied to sample data as in your question

with your_table as (
  select '2021-12-31' date_string union all
  select '2021-12-25T15:45:00' union all
  select '2021-11-15' union all
  select '2021-12-08T09:00:00' union all
  select '2021-12-31' 
)

output is