# Using TODAY function with QUERY

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

• – Rubén
Aug 25, 2019 at 6:23

``````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

• 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