Converting String to date in Databricks SQL returns null

Question

I have a table with string column named Date which contains the values (dd-Mon-yy format data and yyyy-mm-dd format data )

01-jan-21
01-feb-21
01-mar-21
2021-01-01
2021-02-01

I was trying to convert it into a date format of yyyy-mm-dd for all the members

to_date(date,'yyyy-mm-dd') but it returns null .

How to convert a string to specific date format as yyyy-mm-dd in Spark SQL?

Answer 1

As you have two different date types, you need to specify them separately:

select coalesce(to_date(date,'yyyy-MM-dd'), to_date(date,'dd-MMM-yyyy'))

Answer 2

You need to determine the format of the column. In this case, you can just use the length:

(case when length(date) = 10 then to_date(upper(date), 'yyyy-MMM-dd')
      else to_date(date, 'yyyy-mon-dd')
 end)