How to deal with a column that has just date (%m-%d-%Y) and datetime ('%m-%d-%Y %H:%i') records in the same column in AWS Athena?

Question

I'm trying to convert a date column of string type to date type.

I use the below query in AWS Athena:

SELECT col0, col1, col2, date_parse(replace(col3, '/', '-'), '%m-%d-%Y') AS start_date
FROM "bucket"."table"
WHERE col3 <> ''

This works for some records because some dates are formatted like this: 12/08/2019 But it fails for other dates which are formatted like this: 7/1/2019 0:06 Some other dates are formatted like: 2020/04/10 08:39

These different format dates exist in the same column. Dealing with dirty data here... I understand why my query fails because I only have '%m-%d-%Y' format in there. Just wondering how would I deal with something like this so that it can deal with all 3 formats in one query.

Answer 1

You can run through the various combinations like so using a combination of Coalesce and try.

The various date time formats are available from this presto documentation page.

SELECT
    col0,
    col1,
    col2,
    Coalesce(
        try(date_parse(col3, '%m/%d/%Y')),
        try(date_parse(col3, '%Y/%m/%d %H:%i')),
        try(date_parse(col3, '%e/%c/%Y %k:%i'))
    ) AS start_date
FROM "bucket"."table"
WHERE col3 <> ''