Date_Parse INVALID_FUNCTION_ARGUMENT: Invalid format

Hi,

Please check on the below working solution:
sample string is “2010-12-23 00:00:00”
query:
SELECT date_parse(‘2010-12-23 00:00:00’,'%Y-%m-%d %H:%i:%s');
Result:

   _col0  

1. 2010-12-23 00:00:00.000
   Note : Format of the date parse should match the sample date string.

I hope above helps.

have a good day :)

Unfortunately, it did not help. Got following error:

Your query has the following error(s):
INVALID_FUNCTION_ARGUMENT: Invalid format: ""

I understand when you say format should match the input and it is matching however its not working.. Not sure why.

I wonder if the format is getting changed while Athena is reading from S3 and hence when I query it does not match. One reason why explicitly specified TEXTFILE as store format but still the same result.

Hi! Did you manage to solve this?
I have the same issue. I have a the column 'date_x' that contains the date of purchase but it appears in Athena as VARCHAR.
And I want to retrieve from the table the lines that have the purchase date between 1st of September and 3rd of November. I tried to use the following condition:
WHERE date_parse(date_x, 'YYYY-MM-DD') BETWEEN date_parse('2020-09-01', 'yyyy-mm-dd') AND date_parse('2020-11-03', 'yyyy-mm-dd')
and i get the same error as you:
INVALID_FUNCTION_ARGUMENT: Invalid format: "2020-09-01"

Edited by: GabrielaS on Nov 3, 2020 9:04 AM

Edited by: GabrielaS on Nov 3, 2020 9:05 AM

HI man I have been bumping my head against the wall with this one. however, in my case, similar to your case I was using data_format(string_column_name, 'YYYY-MM-dd HH:mm:ss') but I was getting the same error and after looking everywhere I found that since athena uses presto under the hood they both have a similar catch, which is that there is confusing naming between the MySQL data functions and the Java data functions, MySQL functions are data_parse and date_format; the java functions are format_date and parse_datetime. details here https://github.com/prestodb/presto/issues/1019

long story short, for date_parse you shouldn't be using the YYYY-MM-DD format (Java simple date format), instead you should use the %Y-%m-%d format, so the expressions in your query should look like this

date_parse ( date_x, '%Y-%m-%d') assuming date_x follows something similar to the others
date_parse ( '2020-09-01', '%Y-%m-%d')
date_parse ( '2020-11-03', '%Y-%m-%d')

in my specific scenario I was trying to parse a column where the values where strings that look like "2020-01-20 00:00:00" and I was getting the invalid format error; once I changed the format to the %Y etc form, I still go the error, but, it was because the function was evaluating the double quotes as part of the string so I had to account for those on my format and my expression ended up looking like this:

date_parse(string_date_column,'"%Y-%m-%d %H:%i:%s"')

hope this helps

Edited by: Elruncho35 on Nov 24, 2020 6:03 PM

Edited by: Elruncho35 on Nov 24, 2020 6:07 PM