BigQuery SQL: Given string input. how to confirm it is a valid date-CodePudding

I am working on filter the records that don't have valid dates associated with them. The expectant is a string of format yyyymmddhhmmss . How can I validate that the string is actually a valid date? For example the input string could be: 202101....## (invalid literals)

CodePudding user response：

You can use safe.parse_datetime('%Y%m%d%H%M%S', col_string) as col_datetime - if col_string represents valid datetime - it will output that valid datetime, otherwise - null, as in dummy example below

with your_table as (
  select '20211215031521' col_string union all 
  select '20211215031521Y' union all 
  select '202112150H1521' 
)
select col_string, 
  safe.parse_datetime('%Y%m%d%H%M%S', col_string) as col_datetime
from your_table

with output

CodePudding user response：

Would a filter like ...

WHERE SAFE_CAST (datestring AS DATE) IS NOT NULL

serve your purpose?