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

APIDT (1995-09-27 ) its in string type – vinsent paramanantham Sep 14 '17 at 10:27 — vinsent paramanantham, Sep 14 '17 at 10:27

score 22 · Answer 1 · answered Sep 14 '17 at 19:40

22

The correct query for parsing a string into a date would be date_parse.

This would lead to the following query:

select date_parse(b.APIDT, '%Y-%m-%d') from APAPP100 b

prestodb docs: 6.10. Date and Time Functions and Operators

answered Sep 14 '17 at 19:40

jens walter

13,269
2
56
54

1

getting the following error when tried to excute the above select `INVALID_FUNCTION_ARGUMENT: Invalid format: "12633 " is malformed at "3 "` – vinsent paramanantham Sep 15 '17 at 05:48
1

This looks like an invalid format for a date. So . I would rather look at the input data and not the conversion of that data. "12633 " is in any pattern, not a date. – jens walter Sep 15 '17 at 09:51
1

APIDT 1995-09-27 (this is of type string in the table) I am unable to specify as date format and timestamp format for the column I am getting the following error when i tried to specify the column as date `FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. java.lang.UnsupportedOperationException: Parquet does not support date. See HIVE-6384` – vinsent paramanantham Sep 15 '17 at 12:26

Alex Hague · Answer 2 · 2018-04-29T03:48:28.067

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

score 14 · Answer 3 · edited Mar 01 '19 at 06:04

14

This worked -

cast(from_iso8601_timestamp(createdat) as date)

edited Mar 01 '19 at 06:04

Bhargav Rao

50,140
28
121
140

answered Feb 28 '19 at 13:48

Sagar Patil

153
1
6

You sir, are a hero. – BuffMcBigHuge Apr 02 '19 at 14:21

score 1 · Answer 4 · edited Feb 21 '18 at 05:26

1

SELECT   b.APIDT,
         b.Appppppppp,
         date_diff('day',current_date, date(b.APIDT)) AS Duedays
FROM xyz100 a
WHERE regexp_like(b.apidt, '[0-9]{4}-[0-9]{2}-[0-9]{2}')

WHERE NOT regexp_like(b.apidt, '[0-9]{4}-[0-9]{2}-[0-9]{2}') to exclude the junk date

edited Feb 21 '18 at 05:26

answered Sep 19 '17 at 14:09

vinsent paramanantham

953
3
15
34

Athena date format unable to convert string to date formate

4 Answers4