Getting null while converting string to date in spark sql

Question

I have dates in the format '6/30/2020'. It is a string and I want to convert it into date format.

List of methods I have tried

Cast('6/30/2020' as date) #returns null
to_date('6/30/2020','yyyy/MM/dd') #returns null

I also tried splitting the string and then concatenating it into data. After trying all this and putting all the possible combinations in the to_date function, I am still getting the answer as null.

Now I am confused as I have used all the functions to convert string to date. Thanks in advance for your help.

Answer 1

The date format you used was incorrect. Try this:

select to_date('6/30/2020', 'M/dd/yyyy')

If you want to format your result, you can use date_format:

select date_format(to_date('6/30/2020', 'M/dd/yyyy'), 'yyyy/MM/dd')

Note that to_date converts a given string from the given format, while date_format converts a given date to the given format.