Menu
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?
877
Previously, BigQuery executed queries using a non-standard SQL dialect known as BigQuery SQL. With the launch of BigQuery 2.0, BigQuery released support for standard SQL, and renamed BigQuery SQL to legacy SQL. Standard SQL is the preferred SQL dialect for querying data stored in BigQuery.
Datetime type: comprises both calendar date and time. It does not store time zone information: YYYY-MM-DD HH:MM:SS (e.g. ). Timestamp type: comprises date, time, and time zone information.
BigQuery automatically flattens nested fields when querying. To query a column with nested data, each field must be identified in the context of the column that contains it. For example: customer.id refers to the id field in the customer column.
Subtracting a specific amount of days, weeks, months, quarters, or years from a date can be done using the DATE_SUB function. The first argument takes a date and the second argument takes an interval, a numeric value, and a unit. The supported units (DATE_PART) are: Days: DAY.
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()=""
83
This solution wasn't working for me:
DATE(Date) = "2016-07-11"
Instead, I had to use:
Date = TIMESTAMP("2016-07-11")
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
