date type conversion error

ashkd7310
New Contributor II

‎07-22-2024 07:55 PM

Hello,

I am trying to convert the date in MM/dd/yyyy format. So I am first using the date_format function and converting the date into MM/dd/yyyy. So it becomes string. However, my use case is to have the data as date. so I am again converting the string into date by using to_date function. However, it becomes null when I am doing that way. 

I have tried many ways but unable to solve. Is this something databricks doesn't support to store the date in the format of  MM/dd/yyyy?

szymon_dybczak
Esteemed Contributor III

‎07-22-2024 09:51 PM

Hi @ashkd7310 ,

It's not possible. Spark does not support date types formatted in some other way except for yyyy-MM-dd. If you need another format, you will need to again convert the date type into string type, but with the format which you need.

 

 

Rishabh-Pandey
Databricks MVP

‎07-23-2024 12:27 AM

Check with this method if it works.

# Convert date to MM/dd/yyyy format (string)
df = df.withColumn("formatted_date", date_format("date", "MM/dd/yyyy"))

# Convert string back to date
df = df.withColumn("converted_date", to_date("formatted_date", "MM/dd/yyyy"))

 

 

Rishabh Pandey