Athena greater than condition in date column

Question

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!

Answer 1

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

Answer 2

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');

Answer 3

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