Search code examples
sqlamazon-web-servicesamazon-athenaprestotrino

Need to convert string value in Date Format in Athena

I am new in Athena. I am facing one challenge like below: I have a column, date value like Nov 29, 2022 11:26:00 PM. But I need this value as YYYYMMDD format in Athena either Integer or String format. Can anyone help me to convert this value.

Example: Nov 29, 2022 11:26:00 PM it should be 20221129

Solution

  • There is not enough to fully determine the original format, but in essence you need to first parse date and then format it back:

    select date_format(
            date_parse('Nov 29, 2022 11:26:00 PM', '%b %d, %Y %r'),
            '%Y%m%d'
        );

    More info on concrete format identifiers can be found in the docs (or in this one). Potentially you might need to change %r (Time, 12-hour, hh:mm:ss followed by AM or PM) and maybe %d(Day of the month, numeric 01 .. 31).