Working with Date and Time in SQL in Google Sheets

‍

As Google Sheets has its own special format for dates, using and working with dates can be quite tricky. For example, if you run the following query:

select * where A contains '2020-06-30'

You will get the following result:

Google Sheets interpreted the command as looking for rows which include ‘2020-06-30’ stored as a string, instead of that string stored as a date. To solve that, you need to add a clause that indicates the string as a date in a specific format. 

The DATE clause

It turns out that a simple tweak can be made to our original clause. Just add the DATE clause after the CONTAINS clause and before the date enclosed in single quotation marks. The date should have the following pattern:

YYYY-MM-DD

For our previous clause that means we can write the new clause as follows:

select * where A contains date '2020-06-30'

The result is now:

The DATE clause ensures that the following input is accurately interpreted by Google Sheets as a date and read from the date and time stamp in the source sheet. 

Getting entries filed before or after a certain date

To find entries filed before or after a certain date, use a similar syntax to our previous example, but change CONTAINS to either a greater than sign > or a less than sign <. 

The greater than sign > is used to look for entries after a certain date and time.

The less than sign < is used to look for entries before a certain date or time. 

For example, we have a source sheet containing entries from 2020/06/29 to 2020/07/21. We want to query entries made before 2020/07/04. The query looks like this:

select * where A < date '2020-07-04'

The whole formula is:

=query(loaforders!A2:G46,"select * where A < date '2020-07-04'")

The result is:

Using similar syntax, we can query for entries filed after a certain date. Let’s say we want to query entries made after 2020/07/17. The query looks like this:

select * where A > date '2020-07-17'

The whole formula is:

=query(loaforders!A2:G46,"select * where A > date '2020-07-17'")

The result is:

The only problem with this query is that the given date is still included in the results. To exclude those entries, move the date forward by one day. The issue will be discussed in the section titled Small tweak to include and/or exclude the given dates below.

Finally, note that this works whether you have entries marked by date or entries marked by timestamp, which contains both the date and time of entry.

Getting entries filed between two dates

You can also query for entries filed between two dates. Using our previous sheet, we want to get entries filed between 2020-07-05 and 2020-07-10. To do so, we make the following query:

select * where A > date '2020-07-05' AND A < date '2020-07-10'

The whole formula is:

=query(loaforders!A2:G46,"select * where A > date '2020-07-05' AND A < date '2020-07-10'")

And the result is:

Small tweak to include and/or exclude the given dates

As you have noticed in the previous examples, using the greater than and less than signs can exclude the final date from the results. For our example of entries between the two dates, the entries for the date 2020-07-05 were included while the entries for the date 2020-07-10 were excluded. We can illustrate that by using a timeline diagram:

By default, a date is assumed to have a time of 00:00:00, which is midnight. Therefore, when running this query, you will end up having entries from 2020-07-05 00:00:00 to 2020-07-10 00:00:00. This means that, using the original query, the entries that would have been marked 2020-07-10 won’t be included in the query results. To include those entries, you can simply adjust it to the next date, which is 2020-07-11 00:00:00. The query would then be:

select * where A > date '2020-07-05' AND A < date '2020-07-11'

The whole formula is:

=query(loaforders!A2:G46,"select * where A > date '2020-07-05' AND A < date '2020-07-11'")

And the result is:

The DATETIME clause

We can also include the time in querying entries. For that, we will use the DATETIME function. It is used to indicate the data as containing both the date and the time. The data should have the following format:

YYYY-MM-DD HH:mm:ss

To show how it works, let’s look at an example. We wish to query the sheet of entries made before 11 AM and after 3 PM. For querying entries made before 11 AM, here is the actual query:

select * where A < datetime '2020-06-29 11:00:00'

Where the actual entry contains a date and a time. The whole formula is:

=query(source!A2:H46,"select * where A < datetime '2020-06-29 11:00:00'")

And the result is:

For querying entries made after 3 PM, here is the actual query:

select * where A > datetime '2020-06-29 15:00:00'

The whole formula is:

=query(source!A2:H46,"select * where A > datetime '2020-06-29 15:00:00'")

And the result is:

For querying entries between 11 AM and 1 PM on 2020-06-29, here is the actual query:

select * where A > datetime '2020-06-29 11:00:00' and A < datetime '2020-06-29 13:00:00'

The whole formula is:

=query(source!A2:H46,"select * where A > datetime '2020-06-29 11:00:00' and A < datetime '2020-06-29 13:00:00'")

And the result is:

Summary of query forms

Each of the query forms that we discussed are summarized below. We modified the query forms a little: the clauses are all caps while the information is in small caps. The information you need to input will be clearly named as such: