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.

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