Parameterized Query in AWS Athena - Date

Question

SELECT *
FROM tvseso_h
WHERE DATE(tvseso_h.call_creation_date) BETWEEN ? AND ?;

the call_creation_date datatype in source table is in TIMESTAMP and when i created the view as tvseso_h for the same field declared datatype  as DATE .

input date = 20240401
when i run this above query getting error as below TYPE_MISMATCH: line 3:41: Cannot check if date is BETWEEN integer and integer.

Arun · Answer

Hello,

The issue seems to be format in which the value of the variable is getting passed. The query is expecting a DATE value and you seem be passing an integer value.

As mentioned in the below extract from [1] many users were able to get around the issue by using the CAST function.

———————

If necessary, you can use the CAST function when you enter a value for a parameterized term. For example, if you have a column of the date type that you have parameterized in a query and you want to query for the date 2014-07-05, entering CAST('2014-07-05' AS DATE) for the parameter value will return the result.

———————

[1] https://docs.aws.amazon.com/athena/latest/ug/querying-with-prepared-statements.html#querying-with-prepared-statements-considerations-and-limitations

================

Have a nice day!

Parameterized Query in AWS Athena - Date

Relevant content