How to successfully convert string to date type in AWS Athena?

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

I use the below query in AWS Athena:

SELECT a, b, date_parse(date_start, '%m-%d-%Y') AS date_start
FROM "database"."table"

If I add a limit 10 , it works. But when I try to do it to all records I get this error: INVALID_FUNCTION_ARGUMENT: Invalid format: "5/16/2020" is malformed at "/16/2020"

All dates in the string type column look like this – 2/22/2020 and it converted that correctly. I don’t understand why it won’t do the same for the rest.

An additional question – I’m testing the conversion using the SELECT statement above – if I figure this out what would be a next step? Can I do an UPDATE TABLE in AWS Athena?

Answer

Do your date strings look like '2-22-2020' or '2/22/2020'?

If you have separator '/', as shown in your error message, then you need to adapt your format specifier:

SELECT a, b, date_parse(date_start, '%m/%d/%Y') AS date_start
FROM "pg_orangeboydata"."kanopy"

You can also manage both formats at once:

SELECT a, b, date_parse(replace(date_start, '/', '-'), '%m-%d-%Y') AS date_start
FROM "pg_orangeboydata"."kanopy"