Athena greater than condition in date column

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!

3 Answers

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 

You can also use the date function which is a convenient alias for CAST(x AS date):

SELECT * FROM date_data WHERE trading_date >= DATE('2018-07-06'); 

select * from my_schema.my_table_name where date_column = cast('2017-03-29' as DATE) limit 5