PARSE_DATE : Invalid result from parsing function BigQuery/Standard SQL

Question

I have the following code in BigQuery (standard SQL):

SAFE_CAST (PARSE_DATE('%Y-%m-%d',JSON_EXTRACT_SCALAR(g.p_dataforanalytics,'$.birthday') ) as string)

This fails due to:

Invalid result from parsing function

The problem is that I have no control of the data I get in g.p_dataforanalytics some of it is pure garbage and not in the right format, sometimes it's not even reasonable value. Is there a way to define that if PARSE_DATE() fails it will return NULL and not throw the query out?

Basicly I'm looking for something like a try/catch in any programming language?

Answer 1

Use the SAFE. prefix on the parse:

SAFE.PARSE_DATE('%Y-%m-%d', JSON_EXTRACT_SCALAR(g.p_dataforanalytics, '$.birthday') )

I don't see the utility of converting this back to a string, unless you wanted a solution like this:

COALESCE(FORMAT('%Y-%m-%d',
                COALESCE(SAFE.PARSE_DATE('%Y-%m-%d', JSON_EXTRACT_SCALAR(g.p_dataforanalytics, '$.birthday'), 
                         SAFE.PARSE_DATE('%m/%d/%Y', JSON_EXTRACT_SCALAR(g.p_dataforanalytics, '$.birthday')
                        ) 

                ), JSON_EXTRACT_SCALAR(g.p_dataforanalytics, '$.birthday'
        )

That is, if the code tested different formats for the date, chose one, turned it back into YYYY-MM-DD format. And, if none of the formats worked, then kept the original value.