Google Sheets QUERY: Dates and Times

To perform a query based on a date, a time, or a date that also contains times, you will need to use DATE, TIMEOFDAY, or DATETIME, depending on the case.

DATE

For example, to get all data whose date (B) is greater than May 31, 2023, enter B > followed by the keyword DATE and the date in quotation marks '2023-05-31' (in yyyy-MM-dd format):

=QUERY(DB!A1:G15,"SELECT * WHERE B > DATE '2023-05-31'")

google sheets query function select date time

DATE Based on a Cell

If the date of the condition is contained in a cell, use the TEXT function to display it in text format and in the correct format:

=QUERY(DB!A1:G15,"SELECT * WHERE B > DATE '"&TEXT(I2,"yyyy-MM-dd")&"'")

DATE Based on Today

For example, to get all the data whose date (B) is already more than 60 days old, enter:

=QUERY(DB!A1:G15,"SELECT * WHERE B < DATE '"&TEXT(TODAY()-60,"yyyy-MM-dd")&"'")

google sheets query function select date today time

TIMEOFDAY

The use of TIMEOFDAY is similar to DATE but for hours this time (a small database was created for this example since the database does not contain hours).

To get all data whose time (F) is greater or equal to 12:00:00, enter TIMEOFDAY followed by the time in quotation marks (in HH:mm:ss format):

=QUERY(E1:G7,"SELECT * WHERE F >= TIMEOFDAY '12:00:00'")

google sheets query function timeofday hour date time

Or if the time is contained in a cell, enter:

=QUERY(E1:G7,"SELECT * WHERE F >= TIMEOFDAY '"&TEXT(I2,"HH:mm:ss")&"'")

DATETIME

When a date also contains hours, use DATETIME instead of DATE.

To get all data whose date (F) is greater than 01/08/2023 16:00:00, enter DATETIME followed by the date and time in quotation marks (in yyyy-MM-dd HH:mm:ss format):

=QUERY(E1:G7,"SELECT * WHERE F >= DATETIME '2023-08-01 16:00:00'")

google sheets query function datetime date hour time

Or if the date is contained in a cell, enter:

=QUERY(E1:G7,"SELECT * WHERE F >= DATETIME '"&TEXT(I2,"yyyy-MM-dd HH:mm:ss")&"'")