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?
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'))
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)
Our own date formater in spark SQL
Select date.split('-')[2]||'-'||case when length(date.split('-')[0]) = 1 then '0'||date.split('-')[0] else date.split('-')[0] end || case when length(date.split('-')[1]) = 1 then '0'||date.split('-')[1] else date.split('-')[1] end]
Date = date_column
Date format yyyy-mm-dd
Delimiter can be different.
Without any date format.
