1

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

General Grievance
  • 4,555
  • 31
  • 31
  • 45
Tu PHAN
  • 59
  • 6
  • Can you maybe share a copy of your spreadsheet so we can have a closer look? – JPV Oct 22 '20 at 08:32
  • What format does the `A1:C` range have? Moreover, can you share a copy of your sheet as well? @TuPHAN – ale13 Oct 22 '20 at 09:32

1 Answers1

1

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.

General Grievance
  • 4,555
  • 31
  • 31
  • 45