I have a table with a text column in the following format:
5/30/2021 9:35:18 AM
I'm trying to convert this to date(yyyy-MM-dd) but I get null values when I use cast, to_date.
Is there any way to get the above data in the yyyy-mm-dd format?
Add a comment
|
1 Answer
Use Databricks Datetime Patterns. According to SparkSQL documentation on the Databricks website, you can use datetime patterns specific to Databricks to convert to and from date columns. First, you need to convert the text column to a date column like this:
to_date('5/30/2021 9:35:18 AM','M/d/y h:m:s a')
M - month, d - day of month, y - year, h - hour of day (12-hour), m - minute of hour, s - second of minute, a - AM/PM
Once the column is converted to a date, you can easily use the same datetime patterns to convert it back to a specific format. Use the following command to convert it to the required format:
date_format(to_date('5/30/2021 9:35:18 AM','M/d/y h:m:s a'), 'yyyy/MM/dd')
[removed extraneous word "date" in date_format() function]
Note: Depending upon whether you're getting zero left padded days, months, hours, minutes, and seconds, you'll need to tweak the above command.
