BigQuery compare Date

Question

In BigQuery table what I using some date stored in DateTime typed columns with value like "2016-01-20T00:00:00". I want to run query that for example show row which included in some range (lets say from 2016-01-01 to 2016-02-28).

So problem is that when I put

...Where data < TIMESTAMP('2017-01-30 00:00:00')...//less then

it show me nothing but when I change it to opposite value like

...Where data > TIMESTAMP('2017-01-30 00:00:00')...//greater then

it returns me even those values that logically should be excluded (like 2017-01-20)

I have tested that TIMESTAMP('2017-01-20 00:00:00') returning '2017-01-26 00:00:00 UTC'.

So which method from bigQuery DateTime I should use to be able to compare dates that I have? May be I need to convert both of them into some kind 'milliseconds since' value?

Thanks

Answer 1

Thank you all for help. Your answers and link has leaded me to solution.

where data<cast('2017-01-23 00:00:00' as datetime)

cast('2017-01-23 00:00:00' as datetime) provide '2017-01-23T00:00:00' value and bigQuery are happy with it and give me wanted result.

Answer 2

If your column is of datetime int:

Where data < 20170130

and

Where data > 20170130

If your column is of datetime datatype, drop the TIMESTAMP:

Where data < '2017-01-30 00:00:00'

and

Where data > '2017-01-30 00:00:00'