Convert string date to date after changing format

[shanmukh_b]

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_DT	EFF_DT_2	EFF_DT_3
2000-01-14	01/14/2000	2000-01-14

[-werners-]

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’ .