How do I convert a datetime to a date in Google Bigquery using standard SQL or how do I extract the year in a where statement from datetime?

Currently I have a datetime field that I need to filter my WHERE statement by, specifically for years 2019 and 2020. If I just have a date field without time, I know I can use:

WHERE extract(year from cast([field name] as date))> 2018 

to extract the year for example.

Because of this I need one of two things:

1. I need to either transform the datetime field into a date so I can use the above extract sql, OR

2. I need a WHERE statement involving the datetime field that allows me to only see data > years 2018.

To transform the datetime to date, I have tried the convert and left functions, in addition to most of the bigquery guide solutions online, which have unfortunately not worked for me.

Here is an example of the current datetime format: “2018-08-22 02:48:56”

Thanks in advance for any help!

Here’s an image of the error I get when extracting

Answer

I would recommend against applying date functions on the column being filtered, since this incurs more work for your database (every value in the coumn must be converted before being compared), and prevents the database from taking advantage of an existing index.

Instead, you can compare the datetime column to a litteral, like so:

where mydatetimecol >= datetime(2019, 1, 1, 0, 0, 0)

If you have dates in the future, than you can use an half-open interval to do the check:

where 
    mydatetimecol >= datetime(2019, 1, 1, 0, 0, 0)
    and mydatetimecol < datetime(2021, 1, 1, 0, 0, 0)

---

It looks like you are storing dates as strings, in YYYY-MM-DD HH:MI:SS format. If so, you can do string comparison instead (since, fortunately, this format allows it):

where 
    mydatetimecol >= '2019-01-01 00:00:00'
    and mydatetimecol < '2021-01-01 00:00:00'