Cast string to a timestamp in Databricks

Question

When I try to cast a string column with cast(my_value as timestamp) (e.g. values equal 4/24/2020 14:43:54 or 12/5/2020 14:43:54) with Databricks SQL I got the following error :

CAST_INVALID_INPUT] The value '4/24/2020 14:43:54' of the type "STRING" cannot be cast to "TIMESTAMP" because it is malformed. Correct the value as per the syntax, or change its target type. Use `try_cast` to tolerate malformed input and return NULL instead. If necessary set "ansi_mode" to "false" to bypass this error.

I can try working with strings and doing some regex to create the 3 or 4 case possible, but does Databricks SQL can parse this kind of string by design simply ?

Answer 1

You need to use built-in to_timestamp function:

SELECT to_timestamp('12/5/2020 14:43:54', 'MM/d/yyyy HH:mm:ss');