Convert string date to date after changing format

shanmukh_b
New Contributor

‎08-07-2023 11:29 AM

Hi,

I am using Data bricks SQL and came across a scenario. I have a date field whose dates are in format of 'YYYY-MM-DD'. I changed their format into 'MM/DD/YYYY' using DATE_FORMAT() function.

EFF_DT = 2000-01-14

 

 

 

EFF_DT _2 = DATE_FORMAT(EFF_DT, 'MM/dd/yyyy')

 

 

 

DATE_FORMAT() returns a string with with the format 01/14/2000. Now when I am trying to convert this string back to date with the same format using TO_DATE function, I am getting 2000-01-14 instead of 01/14/2000.

 

 

 

EFF_DT _3 =  TO_DATE(DATE_FORMAT(EFF_DT, 'MM/dd/yyyy'), 'MM/dd/yyyy')

 

 

 

I want eff_dt_3 = 01/14/2000 as date. Can anyone help me out.

 

EFF_DTEFF_DT_2EFF_DT_3
2000-01-1401/14/20002000-01-14

 

0 Kudos

-werners-
Esteemed Contributor III

‎08-08-2023 06:47 AM

if you use to_date, you will get a date column as mentioned above.
If you want to use the format MM/dd/yyyy you can use date_format but this will return a string column.

In order to use Spark date functions, Date string should comply with Spark DateType format which is ‘yyyy-MM-dd’ .

0 Kudos