Search code examples
sqlprestoredashperiscope

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

Solution

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