Solved: BigQuery Error : No matching signature for operator = for argument types: DATE, INT64

Problem

Using comparison (=)  on two fields that are of different incompatible data types. Eg. Trying to compare an Integer with Date column. You get the below error message:

No matching signature for operator = for argument types: DATE, INT64. 
Supported signature: ANY = ANY at [1:67]

Solution

#1 Check all the equality signs (=) to make sure the compare columns have the same type

Verify all the equality operators in your SQL query and verify the datatypes on both sides are compatible. (eg. Compare Date with Date)

Example:

Below query compares an Date column (eg. Id) against a Integer

SELECT * FROM `dataset.table` WHERE TranDate= 20201010;

Rewrite the query to below:

SELECT * FROM `dataset.table` WHERE TranDate= '2020-10-10';

#2 Use explicit CAST to convert columns to Date datatype

If you are not hardcoding the date and using a column instead, use the below method

Example:

SELECT * FROM `dataset.table` WHERE TranDate=PARSE_DATE("%Y%m%d", "20201010");

dan-irving

posted on 15 Mar 19