Athena date format unable to convert string to date formate

Question

tried the below syntax none of them helped to convert a string type column to date

select INVC_,APIDT,APDDT from APAPP100 limit 10
select current_date, APIDT,APDDT from APAPP100 limit 10
select date_format( b.APIDT, '%Y-%m-%d') from APAPP100 b
select CAST( b.APIDT AS date) from APAPP100 b
select date(b.APIDT) from APAPP100 b
select convert(datetime, b.APIDT) from APAPP100 b
select date_parse(b.APIDT, '%Y-%m-%d') from APAPP100 b
select str_to_date(b.APIDT) from APAPP100 b

Answer 1

The answer by @jens walter is great if you need to convert a column with a single date format in it. I've had situations where it's useful to have a column that contains multiple different date formats and still be able to convert it.

The following query supports a source column that contains dates in multiple different formats.

SELECT b.APIDT, 
   Coalesce(
     try(date_parse(b.APIDT, '%Y-%m-%d %H:%i:%s')),
     try(date_parse(b.APIDT, '%Y/%m/%d %H:%i:%s')),
     try(date_parse(b.APIDT, '%d %M %Y %H:%i:%s')),
     try(date_parse(b.APIDT, '%d/%m/%Y %H:%i:%s')),
     try(date_parse(b.APIDT, '%d-%m-%Y %H:%i:%s')),
     try(date_parse(b.APIDT, '%Y-%m-%d')),
     try(date_parse(b.APIDT, '%Y/%m/%d')),
     try(date_parse(b.APIDT, '%d %M %Y'))
   ) 
FROM APAPP100 b

The DATE_PARSE function performs the date conversion.

The TRY function handles errors by returning NULL if they do occur.

The COALESCE function takes the first non-null value.

There's a more in-depth write-up here (my blog).