October 5, 2020

How to Use SQL Queries to Search by Date on Google Sheets

Chart with arrows pointing up and down
SECTIONS
  1. How to Find Entries for Specific Date
  2. How to Find Entries Within a Date Range
  3. How About A Calendar View to Select Date Ranges?

One of the most important tasks in data analysis is filtering and sorting data by its attached date and time information. For example, sales trends can only be analyzed if each sale has an attached date and time; else we have no way of knowing how the business fares or whether marketing campaigns work or not.

Therefore, we need to learn how to use Google’s own SQL to filter and sort its data by its attached date. 

This is not a straightforward task, however, and this is partly because a lot of date and time information in several databases are combined into a single entry. Thus, when it is imported into Google Sheets, they are imported as strings instead of dates and times. (If these entries follow a predictable pattern, splitting them into two cells is enough. You can follow this tutorial to learn how to split a cell into two.)

For our example, we imported selected order data from an external database. The order creation date combines the date and time of order creation. This makes the date difficult, if not impossible to analyze. The date and time were split into two separate columns, which was converted by Google Sheets to number variables. This makes them accessible for the QUERY function

A portion of a spreadsheet of order information. Contains the order timestamp as “Created At:, the total price, the customer name, and two columns where the timestamp was split into one column for the date of order and the time it was ordered.

How to Find Entries for Specific Date

If you know how the date is formatted once it is imported to your spreadsheet, finding entries for a specific date is pretty straightforward. The code that we will use will not even require you to split the cells if the date and time are combined into a single entry. The query that you can use is as follows:

select *column_with_needed_data* where *column_containing_the_date* contains *specific_date_enclosed_in_single_quotes*


For our example, we want to look for orders created on September 24, 2020. The query will look like this:

select A, B, C where A contains '2020-09-24'


We can insert it into the query function:

=query(A1:E51,"select A, B, C where A contains '2020-09-24'")


The result would look like this:

The results of the query above. Two orders were filed on September 24, 2020.


How to Find Entries Within a Date Range

This is where our splitting of the date and time into separate cells will enter. Without further ado, here are the query formats for the following:

Finding entries after a certain date

Use the following query to find entries after a certain date:

select *column_with_needed_data* where *column_containing_the_date* > *specific_date_enclosed_in_single_quotes*


Example:

=query(A1:E51,"select A, B, C where A > '2020-09-24'")

Now, we'll get:

Results of the query above. All orders field from September 24 to the current date (October 3, 2020)


Finding entries between two dates

Use the following query to find entries between two dates:

select *column_with_needed_data* where *column_containing_the_date* > *starting_date_in_single_quotes* and *column_containing_the_date* < *ending_date_in_single_quotes*


By default, the starting date is included in the results but the ending date is not. To include the ending date, you can nudge the ending date by one day to get the intended ending date covered.

Example:

=query(A1:E51,"select A, B, C where A > '2020-09-24' and A < '2020-10-02'")

The equation will then show:

Results of the query above. All orders field from September 24 to October 1, 2020.

How About A Calendar View to Select Date Ranges?

Advanced apps and programs that allow selecting data ranges often include a snippet where you can simply click the date range and the graphs are automatically processed to display the data in the specified range. It is possible to format the cells such that they can be converted into drop-down boxes for the date range... 

... Do you want to do all these things, though? 

If you don’t feel like doing these seemingly routine programming work, all just to prepare for analyzing important business data, consider trying Lido. With a few clicks on your laptop, you can now access all the relevant metrics without going through the hassle of accessing the SQL databases of your eCommerce platforms and then coding the formulas to process them. Let our platform do it all for you!

Suscribe to get more data and analytics tips!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.