AWS Athena query error when trying to filter by date

Question

I am trying to use Athena to query some data I have stored in an s3 bucket in parquet format. I have field called datetime which is defined as a date data type in my AWS Glue Data Catalog.

When I try running the following query in Athena, I get the error below:

SELECT DISTINCT datetime 
FROM "craigslist"."pq_craigslist_rental_data_parquet"
WHERE datetime > '2018-09-14'
ORDER BY datetime DESC;

And the error:

Your query has the following error(s):

SYNTAX_ERROR: line 3:16: '>' cannot be applied to date, varchar(10)

What am I doing wrong here? How can I properly filter this data by date?

Susheel · Accepted Answer · 2019-12-05 23:54:24Z

8

the string literal that you provide has to be casted to a date, in order to compare to a date.

where datetime = date('2019-11-27')

edited Dec 5, 2019 at 23:54

answered Nov 27, 2019 at 13:28

Susheel

811 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tanveer Uddin · Accepted Answer · 2018-10-14 01:36:15Z

2

its having issue with the string literal used for date filter. Use WHERE datetime > date '2018-09-14'

answered Oct 14, 2018 at 1:36

Tanveer Uddin

1,54512 silver badges16 bronze badges

3 Comments

0xPeter Over a year ago

Right, but any ideas what the actual problem is or how to fix it?

Tanveer Uddin Over a year ago

looks like implicit date conversion is not working as expected. Most of the time, the conversion to string to float or vice versa happens automatically, hence its called implicit. But in some cases, it can't do it. For those cases, we need to help the interpreter explicitly.

0xPeter Over a year ago

Unfortunately that didn't work either. Still got: SYNTAX_ERROR: line 2:16: '>' cannot be applied to varchar, date

H6_ · Accepted Answer · 2020-03-11 14:36:24Z

1

from_iso8601_date or date should work.

SELECT DISTINCT datetime 
FROM "craigslist"."pq_craigslist_rental_data_parquet"
WHERE datetime > from_iso8601_date('2018-09-14')
ORDER BY datetime DESC;

both return a proper date object.

SELECT typeof(from_iso8601_date('2018-09-14'))

answered Mar 11, 2020 at 14:36

H6_

33k12 gold badges86 silver badges85 bronze badges

Comments

Randomize · Accepted Answer · 2021-06-15 11:30:57Z

0

Bit late here, but I had the same issue and the only workaround I have found is:

WHERE datetime > (select date '2018-09-14')

answered Jun 15, 2021 at 11:30

Randomize

9,15319 gold badges88 silver badges152 bronze badges

Collectives™ on Stack Overflow

AWS Athena query error when trying to filter by date

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related