How to catch a failed CAST statement in BigQuery SQL?

Question

We are converting a STRING field to a DATETIME field using BigQuery's non-legacy SQL.

The DATETIME fields are corrupted with values like "None" and "0.0" which causes our CAST statement to fail.

We see that for other types of SQL there are TRY-CATCH functions and ISNUMERIC() tests - neither of which appear to be supported in BigQuery.

Here's an example that catches "None" but fails to catch random floats or integers:

CASE 
 WHEN UPDT_DT_TM LIKE 'None' THEN NULL
 ELSE CAST(UPDT_DT_TM AS DATETIME) 
END AS UPDT_DT_TM,

Apart from User-Defined-Functions (UDF) in BigQuery - is there any other way in create a CASE statement that can convert to DATETIME when it can but otherwise just leaves the value as NULL?

Answer 1

You can use the SAFE_CAST function, which returns NULL if the input is not a valid value when interpreted as the desired type. In your case, you would just use SAFE_CAST(UPDT_DT_TM AS DATETIME). It is in the Functions & Operators documentation.