I have a spreadsheet (URL below). I've set up a query to make sure I understand how to do a query with a date range. Then I tried to create a similar query on the "real" data. The "Week 1" worksheet feeds the "Week 1 data" worksheet, where I manipulate the data and it contains the query. The data range query returns no results. All other queries work.
={B1:F1; QUERY(ARRAYFORMULA({B2:D, TO_DATE(VALUE(E2:E)), F2:F}),
"select * where Col4 >= date '"&TEXT($G$1, "yyyy-mm-dd")&"'
and Col4 <= date '"&TEXT($G$1+5, "yyyy-mm-dd")&"'", 0)}
=ARRAYFORMULA(IFERROR(VALUE(
{B1:F1; QUERY(ARRAYFORMULA(TO_TEXT({B2:D, VALUE(E2:E), F2:F})),
"select * where Col4 >= '"&VALUE(G1)&"'
and Col4 <= '"&VALUE(G1)+5&"'", 0)}),
{B1:F1; QUERY(ARRAYFORMULA(TO_TEXT({B2:D, VALUE(E2:E), F2:F})),
"select * where Col4 >= '"&VALUE(G1)&"'
and Col4 <= '"&VALUE(G1)+5&"'", 0)}))
3/4
. so you import it as3/4
and internally convert it to3/4/2019
by using cell formatting, but QUERY still sees it as3/4
– MARK MY ANSWER Feb 7 at 16:10