1

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.

Ora Clesismo
  • 11
  • 1
  • Related post about converting string to timestamp using pyspark: https://stackoverflow.com/questions/39088473/pyspark-dataframe-convert-unusual-string-format-to-timestamp – Péter Szilvási Feb 03 '23 at 10:29

2 Answers2

1

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

AOwens
  • 43
  • 4
1
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

inder
  • 61
  • 2
  • Thank you so much, this helped a lot. It actually failed the first time, it turns out the string had an extra space after the AM as inserted into the column "January 1, 2022 12:00:00 AM " Once I trimmed the space the timestamp function worked!! :) Thanks again. –  Ora Clesismo Oct 11 '22 at 13:52