54

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!

Piotr Findeisen
  • 19,480
  • 2
  • 52
  • 82
Eugene Brown
  • 4,032
  • 6
  • 33
  • 47

4 Answers4

88

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
Barry Piccinni
  • 1,685
  • 12
  • 23
  • Thanks for your reply, but this does not solve the issue. That column is already a date class, I expect this is an issue specific to Amazon Athena databases. Your linked fiddle I can see is set to MS SQL Server, a different flavour of SQL in which your answer would be a valid solution. – Eugene Brown Jul 10 '18 at 16:45
  • 1
    @efbbrown The error you have quoted is a syntax error. I doubt it is an Athena issue. Basically the two sides you're evaluating are not the same format. Without specific information about your table (a create statement would be helpful) I guessed that your date column was formatted as a timestamp. If you're sure that your date is in the correct format, then you need to format the right hand side of the evaluation as a date. – Barry Piccinni Jul 11 '18 at 09:26
  • Thanks Barry, you were totally right. I needed to cast the RHS of my evaluation to date. `WHERE observation_date > CAST('2017-12-31' AS DATE)`. – Eugene Brown Jul 11 '18 at 09:46
  • Glad I could help. Updated the answer to reflect this. – Barry Piccinni Jul 11 '18 at 10:27
  • Instead of a CAST, I would use a DATE literal: `DATE '2017-12-31'` like in `WHERE observation_date > DATE '2017-12-31'`. – Piotr Findeisen Jul 12 '18 at 09:51
21

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');
Zerodf
  • 2,208
  • 18
  • 26
2
select * from my_schema.my_table_name where date_column = cast('2017-03-29' as DATE) limit 5
Cristian
  • 548
  • 6
  • 8
2

I just want to add my little words here, if you have date column with ISO-8601 format, for example: 2022-08-02T01:46:46.963120Z then you can use parse_datetime function.

In my case, the query looks like this:

SELECT * FROM internal_alb_logs
WHERE elb_status_code >= 500 AND parse_datetime(time,'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z') > parse_datetime('2022-08-01-23:00:00','yyyy-MM-dd-HH:mm:ss')
ORDER BY time DESC

See more other examples here: https://docs.aws.amazon.com/athena/latest/ug/application-load-balancer-logs.html#query-alb-logs-examples

Chau Giang
  • 1,414
  • 11
  • 19