CODEWITHSUNDEEP
google-bigquery
abhishek jha
abhishek jha

Reputation: 1085

Unable to apply "where" clause to a date column in BigQuery

I have a date column (in YYYY-MM-DD format) in a big query table. I am unable to apply a where clause to the date column. I am using the following queries:

SELECT * FROM [dataSet_Id.TableName] where CR_DT=DATE("2016-01-01")

SELECT * FROM [dataSet_Id.TableName] where CR_DT=DATE("2016-01-01") where CR_DT=20160101

So how do I do it?

Upvotes: 2

Views: 7596

Answers (2)

Uma
Uma

Reputation: 492

I got it work, If I use Standard SQL Dialect instead of Legacy SQL

Sample queries to handle date in where clause:

SELECT * from demoschema.demotable where dob = date('2016-08-10');
SELECT * from demoschema.demotable where dob = '2016-08-11';

If you want to use Standard SQL Dialect, just go to Show options then you will find SQL Version field which is use for enabling Standard SQL. .Dialect.

Upvotes: 4

Graham Polley
Graham Polley

Reputation: 14781

If the type of your CR_DT column is String then:

SELECT * FROM [dataSet_Id.TableName] where CR_DT = '2016-01-01'

If the type of your CR_DT column is TIMESTAMP then:

SELECT * FROM [dataSet_Id.TableName] where DATE(CR_DT) = DATE(timestamp('2016-01-01'))

Upvotes: 2

Related Questions