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

Question

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.

Answer 1

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