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.