Google Sheets QUERY 'where' condition returning empty output when trying to select by date

0
votes

I am learning Google Sheets functions/formulas, and am running into an issue with the QUERY function. I have two separate sheets, and am trying to pull rows/columns from one sheet into the other. Sheet B is where I want to pull the data from. Sheet B has 5 columns, A through E. Rows are added and deleted to this sheet, so the number of rows being queried will vary. I want to pull all rows where column E equals today's date. Here is the code I'm trying:

=query('SheetB'!A5:E100, "SELECT A,B,D,E where E = '" &TODAY()& "'")

Column E is formatted as such: MM/DD/YY

This returns "#N/A", or "Query completed with an empty output"

If I run the above code without the where condition, it returns data as expected. I have no idea why, or what the problem is. Any guidance will be greatly appreciated.

2
dategoogle-sheetsgoogle-sheets-formulagoogle-sheets-querygoogle-query-language

2 Answers

0
votes

Here is the simplest way to do it:

=query('SheetB'!A5:E100, "SELECT * where E = toDate(now())")

Query uses a very specific date format because different regions have vastly different formats. Below is what you would use if you had specific dates.

=query('SheetB'!A5:E100, "SELECT A,B,D,E where E = date '"&TEXT(TODAY(),"yyyy-mm-dd")&"'")

Notice you need to write date preceding 'yyyy-mm-dd' format.

https://developers.google.com/chart/interactive/docs/querylanguage

0
votes

if E column is a valid date it would be:

=QUERY({SheetB!A5:B100, SheetB!D5:E100},  
 "where Col4 = date '"&TEXT(TODAY(), "yyyy-mm-dd")&"'", 0)

or:

=QUERY('SheetB'!A5:E100, 
 "select A,B,D,E
  where E = "&DATEVALUE(TODAY()), 0)