how to format date in databricks notebook sql

Question

I need to convert date vales that are in mm/dd/yyyy format to yyyy-mm-dd in databricks sql. I tried to_date('eff_date', 'mm/dd/yyyy') then i am getting the correct output format but all the rows month values are defaulting to 01. for eg: 12/01/2016 is being converted to 2016-01-01. I tried date_format (to_date('date_column', 'mm/dd/yyyy'), 'yyyy-mm-dd'), in this case all the month values are defaulting to 00 like 2016-00-01. Can someone please advise solution?

Answer 1

You can use below logic:

spark.sql("""
  SELECT TO_DATE(CAST(UNIX_TIMESTAMP('12/01/2016', 'MM/dd/yyyy') AS TIMESTAMP)) AS newdate"""
)

tested :