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?

user_12521932 0
Viewed 296

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.

presto amazon-athena amazon-web-services date sql
2 Answers

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 <> ''
user_134609330

My instinct is to use CASE WHEN col3 LIKE ..., trim off the times for timed flavors, call date parse once with the right second arg format. Also, if you find more exceptions, you can WHEN more LIKE expressions.

user_136735170
Related