Is it possible to safe cast a string to a datetime in Bigquery?

Question

So I have a string column that usually has date data so I do some replaces to get it into proper format and I safe cast to datetime, but all the rows are null when I know the data comes out in proper format, any alternative ideas?

SELECT 
-- what I use to proper format for conversion e.g. '22-04-2022 07:39:00'
REPLACE(REPLACE(TESTDATE, "," , ""), ".", "-") AS x ,

-- place it into safe cast expecting proper dates to cast and others to null
SAFE_CAST(REPLACE(REPLACE(TESTDATE, "," , ""), ".", "-") AS DATETIME FORMAT '%d-%m-%Y %H:%M:%S') AS y,
TESTDATE
FROM TEST_TABLE 

from this i get :

 x                   y       TESTDATE
22-04-2022 07:39:00  null   22.04.2022, 07:39:00

Answer 1

Using '%d.%m.%Y, %T' format string looks enough for your purpose if you want to get DATETIME value.

DECLARE TESTDATE DEFAULT '22.04.2022, 07:39:00';

SELECT TESTDATE, SAFE.PARSE_DATETIME('%d.%m.%Y, %T', TESTDATE);

Answer 2

Use below

SELECT 
-- what I use to proper format for conversion e.g. '22-04-2022 07:39:00'
TRANSLATE(TESTDATE, ".," , "-") AS x ,

-- place it into safe cast expecting proper dates to cast and others to null
SAFE.PARSE_DATETIME('%d-%m-%Y %H:%M:%S', TRANSLATE(TESTDATE, ".," , "-")) AS y,
TESTDATE
FROM TEST_TABLE           

with output