Unable to convert to date from datetime string with AM and PM

[David_Billa]

Any help to understand why it's showing 'null' instead of the date value? It's showing null only for 12:00:00 AM and for any other values it's showing date correctly

TO_DATE("12/30/2022 12:00:00 AM", "MM/dd/yyyy HH:mm:ss a") AS tsDate

[Alberto_Umana]

Hi @David_Billa,

Can you try with:

TO_TIMESTAMP("12/30/2022 12:00:00 AM", "MM/dd/yyyy hh:mm:ss a") AS tsDate

The issue you are encountering with the TO_DATE function returning null for the value "12:00:00 AM" is likely due to the format string not matching the input string correctly. The TO_DATE function in Databricks requires the format string to precisely match the input string for it to parse the date correctly

https://docs.databricks.com/ja/sql/language-manual/functions/to_timestamp.html