CODEWITHSUNDEEP
google-bigquerygoogle-cloud-platform
rereradu
rereradu

Reputation: 334

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

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?

Upvotes: 16

Views: 26507

Answers (2)

Ramiro Agustin Palacios
Ramiro Agustin Palacios

Reputation: 21

This solution wasn't working for me: 

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

Instead, I had to use: 

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

Upvotes: 2

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

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()=""

Upvotes: 21

Related Questions