In this article:

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

>Click here to Import a MYSQL Database to a Lido Spreadsheet

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!

Automate repetitive tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Trigger
Action
Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
ADDCALENDAREVENT
Creates a one- or multiple-day calendar event with optional attendees
ADDHUBSPOT
Adds an object to Hubspot
ADDSLACKCHANNEL
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
CREATEGOOGLEDOC
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL
Sends an email using your Google account
SENDOUTLOOK
Sends an email using your Microsoft Outlook account
SENDSLACK
Sends a Slack message
SENDSMS
Sends an SMS message using your Twilio account
UPDATECELL
Updates cells with given values
UPDATEHUBSPOT
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started