Athena greater than condition in date column

amazon-athena, amazon-web-services, presto, sql

Solution

You need to use a cast to format the date correctly before making this comparison. Try the following:

SELECT observation_date, COUNT(*) AS count
FROM db.table_name
WHERE observation_date > CAST('2017-12-31' AS DATE)
GROUP BY observation_date

Check it out in Fiddler: SQL Fidle

UPDATE 17/07/2019

In order to reflect comments

SELECT observation_date, COUNT(*) AS count
FROM db.table_name
WHERE observation_date > DATE('2017-12-31')
GROUP BY observation_date

Problem

I have the following query that I am trying to run on Athena. ``` SELECT observation_date, COUNT(*) AS count FROM db.table_name WHERE observation_date > '2017-12-31' GROUP BY observation_date ``` However it is producing this error: ``` SYNTAX_ERROR: line 3:24: '>' cannot be applied to date, varchar(10) ``` This seems odd to me. Is there an error in my query or is Athena not able to handle greater than operators on date columns? Thanks!

Original source