2

I have a CSV file having Orderdate as string in it. In Amazon Atena trying to use dateparse to convert the format of data but getting error. This is what i am trying:

select parse_datetime(orderdate,'%m/%d/%y %H:%i:%s') from orders

Error: INVALID_FUNCTION_ARGUMENT: Invalid format: "9/1/2015 15:43"

sim
  • 105
  • 2
  • 3
  • 10
  • Possible duplicate of [Error on query parsing alb logs by datetime in aws athena](https://stackoverflow.com/questions/52146802/error-on-query-parsing-alb-logs-by-datetime-in-aws-athena) – kenorb Jan 31 '19 at 16:15
  • For anyone who has multiple input formats, consider `coalesce(try(first approach), second_approach, ...)`. To be sure that everything is converted, don't use `try` in the last approach. – Asclepius Apr 07 '21 at 22:44

1 Answers1

3

The error means that your format string '%m/%d/%y %H:%i:%s' does not match the orderdate string.

Your orderdate does not have seconds and the year is 4 digits. Change your format string to '%m/%d/%Y %H:%i'

Date and Time Functions and Operators

John Hanley
  • 74,467
  • 6
  • 95
  • 159
  • Updated my answer. I did not notice the 4 digit year issue. – John Hanley Nov 14 '17 at 00:39
  • 1
    ..Thankyou for the update but with this format it is throwing new error INVALID_FUNCTION_ARGUMENT: Illegal pattern component: i – sim Nov 14 '17 at 16:45
  • 1
    My query worked by the string you gave '%m/%d/%Y %H:%i'. only change i made was intead of parse_datetime i used date_parse(orderdate,'%m/%d/%Y %H:%i') and it worked like a charm. Thankyou so much – sim Nov 14 '17 at 19:38