databricks string to timestamp "January 1, 2022 12:00:00 AM"

Question

New to databricks, loaded a table which has a column of string type with values such as "January 1, 2022 12:00:00 AM" and need to convert it to a timestamp. Any suggestions? I've tried a bunch of SQL date functions but they either fail or return NULL. I can add a timestamp column to the table and could insert the converted data to the new column if that would make it easy.

Answer 1

Try a “to_timestamp” function, it takes a string value (from a column) and an optional format. documentation

Answer 2

SELECT to_timestamp("January 1, 2022 12:00:00 AM", 'MMMM d, y h:m:s a')
--output is 2022-01-01T00:00:00.000+0000

Here's the doc for format: https://docs.databricks.com/sql/language-manual/sql-ref-datetime-pattern.html#pattern-table