Using TODAY function with QUERY

Question

I am using this formula (see below) to try and retrieve dates automatically between the current date and the next. However, I keep getting the error, "QUERY completed with an empty output."

=ARRAYFORMULA(QUERY({A3:H, HOUR(A3:A)}, 
 "select Col7,sum(Col3),sum(Col5),sum(Col8) 
 where Col1 is not null and Col9=7 
 and Col1 = 'TODAY()' 
 and Col1 = 'TODAY()+1' 
 group by Col7 
 label sum(Col3)'',sum(Col5)'',sum(Col8)''", 0))​​

Previously I was using THIS formula (see below). But with this one, I would have to change the date manually every day. Not ideal but I was managing.

=ARRAYFORMULA(QUERY({A3:H, HOUR(A3:A)}, 
 "select Col7,sum(Col3),sum(Col5),sum(Col8) 
 where Col1 is not null and Col9=8 
 and Col1 >= date '2019-08-20' 
 and Col1 <= date '2019-08-21' 
 group by Col7 
label sum(Col3)'',sum(Col5)'',sum(Col8)''", 0))​​

Is it possible to use the TODAY() function here or should I be looking at other avenues to solve this?

I have included a sample copy of my workbook for anyone to play with. You will find the first formula in J5 and the second formula in J27.

SAMPLE WORKBOOK

Answer 1

Instead of

date 'TODAY()'

use

date '"&TEXT(TODAY(), "yyyy-mm-dd")&"'"

NOTES:

• The last " should be placed in a proper place in order to have a text value as the second argument of QUERY.
• 'TODAY()' doesn't returns Today's date, it returns a string with the word TODAY followed by parenthesis.

Try this

=ARRAYFORMULA(QUERY({A3:H, HOUR(A3:A)}, 
 "select Col7,sum(Col3),sum(Col5),sum(Col8) 
 where Col1 is not null and Col9=7 
 and Col1 = '"&TEXT(TODAY(), "yyyy-mm-dd")&"' 
 and Col1 = '"&TEXT(TODAY() + 1, "yyyy-mm-dd")&"' 
 group by Col7 
 label sum(Col3)'',sum(Col5)'',sum(Col8)''", 0))​​

Related

• Dynamic date range query
• QUERY most recent date prior to today
• Querying for the last results with group by

Answer 2

• paste this in J5 cell:

  =ARRAYFORMULA(IFERROR(QUERY(QUERY({TO_DATE(INT(A$3:A)), B$3:H, HOUR(A$3:A)},
   "select Col7,sum(Col3),sum(Col5),sum(Col8) 
    where Col1 is not null 
      and Col9 = "&TEXT(INDIRECT("J"&ROW()-1), "h")*1&" 
      and Col1 >= date '"&TEXT(TODAY()-1, "yyyy-mm-dd")&"' 
      and Col1 <= date '"&TEXT(TODAY(),    "yyyy-mm-dd")&"' 
    group by Col7", 0), "offset 1", 0)))

  

---

• then select and copy J5 and paste in J27, J49, etc. wherever you need it