Bigquery Error: No matching signature for operator BETWEEN for argument types: DATE, TIMESTAMP, TIMESTAMP

Problem 🔍

When performing a filter condition using BETWEEN clause on columns of different datatypes, Bigquery will throw the below error:

No matching signature for operator BETWEEN for argument types: DATE, TIMESTAMP, 
TIMESTAMP. Supported signature: (ANY) BETWEEN (ANY) AND (ANY) at [1:81]

Solution 💡

Check if the compare column and the values that are used in the BETWEEN clause are of the same datatype

Example:

In the below example, MY_DATE is of date datatype, its used in the filter with a BETWEEN clause with values that are TIMESTAMP datatype

SELECT MY_DATE FROM `project-name.Dataset2.MY_DATE_DIMENSION` 
where MY_DATE BETWEEN TIMESTAMP("2018-01-21") AND TIMESTAMP("2018-07-21")

To fix the issue, rewrite the query as follows:

SELECT MY_DATE FROM `project-name.Dataset2.MY_DATE_DIMENSION` 
where MY_DATE BETWEEN DATE("2018-01-21") AND DATE("2018-07-21")

Notice: 

• the column MY_DATE is date datatype
• the values used in the BETWEEN clause is also of date datatype

This should fix the issue!

More Troubleshooting tips ⚡

• Though we have shown one example in this guide, you can use the same idea to resolve all the other datatype differences as well. eg. INT64 vs NUMERIC, FLOAT vs NUMERIC,..
• Carefully evaluate the datatypes in the left hand side vs right hand side of the filter conditions and using explicit casting (eg. CAST) to convert them to the same uniform datatype

DataFreak

posted on 09 Feb 19