Need to convert string value in Date Format in Athena

Question

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

Will [this](https://stackoverflow.com/questions/44064923/aws-athena-and-date-format) and [this](https://stackoverflow.com/questions/48152596/amazon-athena-convert-string-to-date) help? — markalex, Mar 24 '23 at 06:56

score 1 · Accepted Answer · answered Mar 24 '23 at 08:46

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

Need to convert string value in Date Format in Athena

1 Answers1