convert '19th October 2018' into date in Presto/SQL
I have a column of strings which have been extracted from email content. The strings are in the format:-
Tuesday 12th March 2019
I can use split_part to drop the Tuesday (anything up to and including 'day' is cut) but that's a lot more difficult for the day of the month part. I can convert this to a date if the 'th' (or 'rd' or 'st') is gone from the day of the month but I can't quite get there.
Any advice gratefully appreciated.
Could you try this :
date_parse('Tuesday 12th March 2019','%W %D %M %Y')
Depending on if the prestosql doc is up to date, this might not work because it is said :
Warning The following specifiers are not currently supported: %D %U %u %V %w %X
EDIT : In addition to gordon's answer you could try to use :
date_parse(regexp_replace('Tuesday 12th March 2019','(\d+)((th|rd|nd|st) )','$1 '),'%W %d %M %Y')
There are a finite number of suffixes, so how about a brute force appraoch:
date_parse(replace(replace(replace(replace(substr(str, position(' ' in str) + 1 ), 'st', '' ), 'nd', '' ), 'rd', '' ), 'th'), '%d %M %Y' )
It would be much simpler if
%D, but apparently it does not.
For anyone interested, I took the advice from both users above which helped me towards the solution. The final bit of code looked like this:-
split_here_3,2),'(\d+)((th|rd|nd|st) )','$1 ')),'%e %M %Y'))
Thanks again for the help!!