Convert string date with three letter month to proper date format

[justinirizarry]

Hi,

I have a data set with state and end subscription dates that are stored as text. I am trying to convert the text to proper date format.

The issue is that the day of the month dates aren't always two digits. For example, I have both

Jun  3 2016 01:06:10:900PM

and

Apr 29 2016 08:23:35:490AM

I would like to eliminate the time stamp altogether and convert just the date to a proper YYYY-MM-DD format.

I have been playing around with this for a little while and need some help! Original file attached. Thank you!

[jrgo · Accepted solution]

@justinirizarry,

The expression below should work on version 11+ as they updated their date parser to allow months and day numbers with or without the leading zero.

DATETIMEPARSE([Subscription End],'%b %d %Y')

If you're running an older version, you will need to correct the single digits and add their leading zero.

[justinirizarry]

Thank you!