Search code examples
type-conversionprestotrino

How to parse month-year string using Presto

I have a column that contains a Month-Year string that I would like to convert to an actual date representing the first day of the Month and Year combination. For example

+----------+------------+
| Original | Desired    |
+----------+------------+
| Aug-19   | 08/01/2019 |
+----------+------------+
| Sep-20   | 09/01/2020 |
+----------+------------+
| May-22   | 05/01/2022 |
+----------+------------+

I have tried breaking apart the Month-Year string using split_part but when I try and pass Month as a parameter into date_parse it throws an error with the input (INVALID_FUNCTION_ARGUMENT). I could break apart the Month-Year into strings and then recombine, hard-coding the 01 however the problem seems that three letter month cannot be parsed into an actual month by Presto. I also want to avoid a 12 line CASE WHEN statement to parse the month if possible.

Solution

  • I'm not sure where the year comes from, but the query will be like this:

    select date_format(date_parse('May-22', '%b-%d'), '%m/%d/%Y')

    https://trino.io/docs/current/functions/datetime.html?mysql-date-functions