How to convert 'Jul 24 2022' to '2022-07-24' in spark sql

Question

I want to convert a string date column to a date or timestamp (YYYY-MM-DD). How can i do it in scala Spark Sql ?

Input:

D1

Apr 24 2022|

Jul 08 2021|

Jan 16 2022|

Expected : D2

2022-04-24|

2021-07-08|

2022-01-16|

Answer 1

You can use to_date and format the input accordingly. Need to make the month characters in uppercase to get it recognized in the pattern.

Refer this to get the format patterns https://spark.apache.org/docs/latest/sql-ref-datetime-pattern.html

select 'Apr 24 2022' D1, to_date(upper('Apr 24 2022'),'MMM dd yyyy') D2
union
select 'Jul 08 2021' D1, to_date(upper('Jul 08 2021'),'MMM dd yyyy') D2
union
select 'Jan 16 2022' D1, to_date(upper('Jan 16 2022'),'MMM dd yyyy') D2