You filter a Google Sheets QUERY by date and get zero rows, a #VALUE! error, or results with a few rows mysteriously missing. Date conditions are the most common place where QUERY goes wrong.
The short answer: inside the query string, write dates as date 'yyyy-MM-dd'. When the date comes from a cell, wrap it in TEXT(cell,"yyyy-mm-dd") before concatenating it. This is because QUERY uses the Google Visualization API Query Language, which defines a date literal as the keyword date followed by a string in yyyy-MM-dd format. If rows are still missing after that, the column contains dates stored as text.
Quick diagnosis by symptom
Symptom | Likely cause | Fix |
|---|---|---|
Zero rows returned | The date is written as a plain string, e.g. | Use |
#VALUE! or parse error | A cell date was concatenated directly and became a serial number | Concatenate |
Some rows missing | Text dates mixed into the column are treated as null | Convert the source data to real dates |
Month filter is off by one |
| Use |
Today's rows missing in a timestamp column | Datetime values compared with a date only | Use "less than tomorrow" or |
1. Write dates as date 'yyyy-MM-dd'
The second argument of QUERY is not a spreadsheet formula. Google's QUERY help page describes it as a query "written in the Google Visualization API Query Language", and that language has its own literal formats:
Type | Format | Example |
|---|---|---|
date |
|
|
datetime |
|
|
timeofday |
|
|
- Wrong:
=QUERY(A:C,"select * where A >= '10/1/2026'")(compares a date with a string, returns nothing) - Right:
=QUERY(A:C,"select * where A >= date '2026-10-01'")
Use hyphens, not slashes, and always two-digit months and days. Your spreadsheet's locale display format does not matter here; the literal is always yyyy-MM-dd.
2. Use a date from a cell with TEXT()
In practice you usually want the start date in a cell. Writing "... where A >= date '"&F1&"'" inserts the date's internal serial number (for example 46296 for October 1, 2026), which is not a valid date literal. Convert it first:
=QUERY(A:C,"select * where A >= date '"&TEXT(F1,"yyyy-mm-dd")&"'")- Date range:
=QUERY(A:C,"select * where A >= date '"&TEXT(F1,"yyyy-mm-dd")&"' and A <= date '"&TEXT(G1,"yyyy-mm-dd")&"'")
For "from today", put =TODAY() in a cell and reference it the same way. The query language does have now(), but its reference says it returns the current datetime in GMT, so in other time zones "today" can be off by several hours.
With three kinds of quotes in one formula, it helps to build the query string in a separate cell and pass that cell as the second argument. Google's help page explicitly allows the query to be a cell reference.
3. Some rows are missing: text dates in the column
If the syntax is right but certain rows never appear, those rows probably hold dates stored as text. Google's QUERY help page states:
In case of mixed data types in a single column, the majority data type determines the data type of the column for query purposes. Minority data types are considered null values.
So a "2026/10/05" that came in as text from a CSV import or manual entry becomes null and fails every date condition. Three quick checks:
=ISDATE(A5)returns FALSE- The value is left-aligned (real dates and numbers are right-aligned by default)
- Changing the format to Number does not turn it into a number
Fix the source: if Format → Number → Date does not convert it, add a helper column with =DATEVALUE(A5) and run QUERY on that column instead.
4. Filtering by month: month() is zero-based
The query language has year(), month() and day(), but as the reference notes, month() starts at 0: January is 0 and February is 1.
- October 2026:
where year(A) = 2026 and month(A) = 9 - Same thing as a range (harder to get wrong):
where A >= date '2026-10-01' and A < date '2026-11-01'
For a monthly count, add 1 at display time: select year(A), month(A)+1, count(B) group by year(A), month(A)+1.
5. Timestamp columns drop today's rows
If column A holds values like "10/24/2026 14:30", it is a datetime column. where A <= date '2026-10-24' then excludes everything after midnight on October 24. To include the whole day, either:
- Use "less than the next day":
where A < date '2026-10-25' - Compare the date part only:
where toDate(A) <= date '2026-10-24'(toDate()returns the date part of a datetime)
With IMPORTRANGE or array literals
When the data argument is the result of IMPORTRANGE or an array like {A:A,C:C}, refer to columns as Col1, Col2 instead of letters; Google's help page notes that QUERY accepts both "Col" and "A, B" notation. Date literals work the same way: where Col1 >= date '2026-10-01'. If you also get #N/A when looking up dates with VLOOKUP, the cause is usually the same text-versus-date mix; see VLOOKUP #N/A in Google Sheets: 5 causes and quick fixes.
When a team ends up with many sheets that each slice data by date, one broken formula can stop reporting for everyone. At Mihata we also help teams turn spreadsheet workflows like this into simple internal apps. If that sounds familiar, feel free to take a look.
Summary
- Inside QUERY, write dates as
date 'yyyy-MM-dd' - For cell dates, concatenate
TEXT(cell,"yyyy-mm-dd") - Missing rows usually mean text dates treated as null
month()is zero-based; ranges avoid the trap- For timestamps, use less than tomorrow or
toDate()
FAQ
Why does my Google Sheets QUERY return no results when I filter by date?
Inside the query string, dates must be written as a date literal: the keyword date followed by a string in yyyy-MM-dd format, for example where A >= date '2026-10-01'. If you write where A >= '2026/10/01', the date column is compared with a plain string and no rows match.
How do I use a date from a cell in a QUERY where clause?
Convert the cell to text with TEXT(cell,"yyyy-mm-dd") and concatenate it into the query, for example =QUERY(A:C,"select * where A >= date '"&TEXT(F1,"yyyy-mm-dd")&"'"). Concatenating the cell directly inserts the date's serial number, which is not a valid date literal.
Why are some rows missing from my QUERY date filter?
The column probably contains some dates stored as text. Google's help page for QUERY says that when a column has mixed data types, the majority type is used and minority types are treated as null, so those rows never match a date condition. Check them with ISDATE and convert them with DATEVALUE.
Why does month() in QUERY return the wrong month?
month() in the Google Visualization API Query Language is zero-based: January is 0 and October is 9. Use where month(A) = 9 for October, or filter by a date range such as A >= date '2026-10-01' and A < date '2026-11-01'.