Date Comparisons in Queries: Why so Complex?

When I first started writing date comparisons within Google queries (pulling data before / after a certain date), it was a pain to figure out the correct syntax.  

=query(D:G, “select E where D greater than date ‘2016-08-22’”)

Unfortunately it’s not really mentioned anywhere in the Google Sheets documentation, so I had to figure it out for myself.  

You have to do two things correctly:

- Write the string ‘date’ before the actual date, to declare your intentions
- Type your date in the format ‘yyyy-mm-dd’

If you want to compare a date dynamically from another cell, it’s a bit of a different story.  I like to keep date ranges (last 7 days, last 30 days, year-to-date, etc), then reference them in a query like this:

=query(D:G, “select E where D greater than date ‘“ & text(B1, “yyyy-mm-dd”) & “‘“)

Notice the syntax:

- To combine the string with an outside value, close the query double-quotes, and use ‘&’ 
- Use the TEXT function to convert the date into the correct ‘yyyy-mm-dd’ format

The Google Sheets Query Function

A weapon of mass laziness

Already enrolled?
Sign in to continue learning.