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.

mck · Accepted Answer · 2021-02-22 10:58:57Z

3

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.

answered Feb 22, 2021 at 10:58

mck

42.7k13 gold badges44 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

BrownBatman Over a year ago

How it is working for 12/30/2020 when the specified date format is 'M/dd/yyyy' and why 6/30/2020 is not working with 'MM/dd/yyyy' when 12/30/2020 is.

mck Over a year ago

because if you specify MM, you need the month to have 2 digits. But 6 only has one digit, so the format is incorrect. Note that the number of M's means the minimum number of digits.

mck Over a year ago

@BrownBatman see the docs for more details on date string formatting.

Collectives™ on Stack Overflow

Getting null while converting string to date in spark sql

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related