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.

Many thanks,

Barry

3 answers

  • answered 2019-11-21 15:22 Gosfly

    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

    presto sql doc

    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')
    

  • answered 2019-11-21 15:25 Gordon Linoff

    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 date_parse() supported %D, but apparently it does not.

  • answered 2019-11-21 16:23 Barry Evans

    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:-

    date(date_parse(trim(regexp_replace(split_part(split_part(split_part(my_data,split_here ,2),'split_here_2,1),split_here_3,2),'(\d+)((th|rd|nd|st) )','$1 ')),'%e %M %Y'))

    which returned:-

    2018-11-04

    2019-04-06

    2018-11-02

    2019-09-19

    2019-07-12

    2018-11-04

    2018-09-29

    2018-10-19

    2018-11-02

    Thanks again for the help!!