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