Google Sheet : Query Date is Today Problem

Question

I have a data in Sheet1 with Col is A, B, C which C is date format YYYY-MM-DD HH:MM:SS, I need to query the data in Sheet1 where C is today date. My formula is

=query('Sheet1!A1:C',"Select * where C = Date'"&text(today(),"YYYY-MM-DD")&"')

But result return is empty even C is today date.

When I change the formula to this format then it works,

=query('Sheet1!A1:C',"Select * where C < Date'"&text(today()-1,"YYYY-MM-DD")&"'and C > Date'"&text(today()+1,"YYYY-MM-DD")&"')

I wonder what was wrong with my first formula when I use "=" in the query.

Thanks

Can you maybe share a copy of your spreadsheet so we can have a closer look? — JPV
What format does the A1:C range have? Moreover, can you share a copy of your sheet as well? @TuPHAN — ale13

Calculuswhiz Calculuswhiz · Accepted Answer · 2020-10-22T11:51:22

The first formula never does any manipulation on C:C. This means that the equality filter only matches when the time is also 0:00:00 of the same day. You can use the toDate Query function to convert C like so:

=query(A1:C,"Select * where toDate(C) = Date'"&text(today(),"YYYY-MM-DD")&"'")

See the doc on scalar functions for documentation.

Google Sheet : Query Date is Today Problem

1 Answers