2

I'm trying to run a query in Athena where the latest event date is the only row that gets returned. Initially, I'm first trying to parse the event date from a string (Partitioned) to yyyy/mm/dd. After that, I want to pull ONLY the MAX() or latest event date details.

Here's what I currently have and the error I'm getting

select CAST(date_parse(eventdate,'%yyyy/%mm/%dd')AS date)

INVALID_FUNCTION_ARGUMENT: Invalid format: "20221204" is malformed at "221204"

Guru Stron
  • 102,774
  • 10
  • 95
  • 132
productworkingenv
  • 21
  • 2
  • Can you provide us with some examples of the input data? – John Rotenstein Apr 06 '23 at 01:01
  • pl refer to this answer, it could be the solution. https://stackoverflow.com/a/52309683/2067753 if this isn't helpful then we are going to need exactly what data type `eventdate` is, and sample of data from that column. – Paul Maxwell Apr 06 '23 at 01:39

1 Answers1

0

I'm first trying to parse the event date from a string (Partitioned) to yyyy/mm/dd

You don't parse TO, you parse FROM and you need to parse providing correct format.

date_parse uses MySQL format specifiers, and your current format does not make much sense compared to the passed date, you need to fix it (both specifiers and delimiter which is not present in date string):

select CAST(date_parse('20221204', '%Y%m%d') AS date);

Output:

   _col0
------------
 2022-12-04

If you want to format the date after that - then just use date_format (cast also not needed):

select date_format(date_parse('20221204', '%Y%m%d'), '%Y/%m/%d');

Output:

   _col0
------------
 2022/12/04
Guru Stron
  • 102,774
  • 10
  • 95
  • 132