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
=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