BigQuery - how to compare a "date" column (using legacy SQL)?

Question

I have a BigQuery table with a column Date which is a date type. I am trying to run this query:

SELECT * FROM dataset.table_name WHERE Date = "2016-07-11"

This throws the error:

Argument type mismatch in function EQUAL: 'Date' is type int32, '2016-07-11' is type string

I have also tried this query:

SELECT * FROM dataset.table_name WHERE Date = TIMESTAMP("2016-07-11")

but this returns 0 results, although my table contains at least one record with this value (2016-07-11) in the Date column.

So, how can I compare a date field in BigQuery?

Answer 1

Try below

WHERE DATE(Date) = "2016-07-11"

My additional recommendation would be to not to use reserved words as column's name, I think if your column was named properly - your original WHERE clause would worked perfectly and you would not need to use workaround with DATE()=""

Answer 2

This solution wasn't working for me:

DATE(Date) = "2016-07-11"

Instead, I had to use:

Date = TIMESTAMP("2016-07-11")