Notifications
Mark all as read
Q&A

Querying date time values for date only in Google Sheets

+0
−1

An as yet unanswered but recent question on Stack Overflow Google Sheet : Query Date is Today Problem asked by @Tu PHAN was:

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.

What is wrong and what would work?

Why does this post require moderator attention?
You might want to add some details to your flag.
Why should this post be closed?

0 comments

1 answer

+0
−0

In calculations involving time TODAY() has an implied time value of midnight at the start of the day, or 0:

provides the current date with no time component.

I think both the syntax and the logic of the formula that was claimed to work are wrong. Something a little simplified that should produce results is:

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

today()-1 includes all of yesterday after the start of yesterday. If times are not required in the output, an alternative is to reconstruct the source data with times stripped out before applying something like:

=query(ArrayFormula({Sheet1!A:B,Text(Sheet1!C:C,"YYYY-MM-DD")}),"where Col3 = '"&text(Today(),"YYYY-MM-DD")&"'")

With correcting the syntax in:

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

the results should be only for entries with today's date with times for midnight at the start of today, or without time.

Why does this post require moderator attention?
You might want to add some details to your flag.

0 comments

Sign up to answer this question »