How to filter on date in Big query

Question

I'm trying to figure out how to properly filter on dates in big query. For instance in MS sql I do:

Select a, b, date from table_1 where date >= '2017-05-01' 

In big query the filter doesn't work and it returns all the records. I tried

Select a, b, date from table_1 where date >= Date("2017-05-01")
Select a, b, date from table_1 where date >= 2017-05-01
Select a, b, date from table_1 where date >= timestamp('2017-05-01')

None of those worked. The documentation mentioned creating a partitioned table, but I'm not sure that is what I need to do ( and I hope not).

Please help :(

Answer 1

The column date is a datetype

Try below example

#standardSQL
WITH table_1 AS (
  SELECT 1 AS a, 2 AS b, DATE '2017-04-20' AS date UNION ALL
  SELECT 1, 2, DATE '2017-05-20' 
)
SELECT *
FROM table_1
WHERE date > '2017-05-01'

the output is

a   b   date     
1   2   2017-05-20   

Answer 2

I suspect that your date is actually a string. To use it in date comparisons, you can first parse it as a date. For example,

#standardSQL
SELECT a, b, date
FROM table_1
WHERE PARSE_DATE('%Y%m%d', date) >= '2017-05-01';

You can try the query with some sample data:

#standardSQL
WITH table_1 AS (
  SELECT 1 AS a, 'foo' AS b, '20170501' AS date UNION ALL
  SELECT 2, 'bar', '20170101' UNION ALL
  SELECT 3, 'baz', '20170625'
)
SELECT a, b, date
FROM table_1
WHERE PARSE_DATE('%Y%m%d', date) >= '2017-05-01';