Reputation: 778
Hi Im unable to figure out whats wrong with my datetime query =QUERY(A:G,"SELECT * WHERE G < datetime '2022-08-24 08:45:00'",1)
in my Google Sheet it should return 2 rows on the left whos datetimes in G are < 2022-08-24 08:45:00
(also supplied screenshot)
Upvotes: 1
Views: 1073
Reputation: 1
It should be:
=QUERY(A:G, "WHERE G < datetime '2022-08-24 08:45:00'", 1)
yyyy-mm-dd hh:mm:ss
is the correct format - https://developers.google.com/chart/interactive/docs/querylanguage#literals
And G2 can't be a text string so multiply it by 1:
=(TEXT(DATE(LEFT(B2,4),MID(B2,5,2),
RIGHT(B2,2)),"yyyy-MM-dd")&" "&TEXT(TIME(C2,D2,0),"HH:mm:ss"))*1
But it's better if you use arrayformula:
=ARRAYFORMULA(IFERROR(DATE(LEFT(B2:B, 4), MID(B2:B, 5, 2),
RIGHT(B2:B, 2))+(TIME(C2:C, D2:D, ))))
Upvotes: 2
Reputation: 2140
Since the QUERY()
function runs a Google Visualization API Query Language query across data it runs slower than the usual formulas in google sheets. This will be very noticeable if you have large data. So another way is using
the FILTER()
function to filter only the date less than '2022-08-24 08:45:00'. This runs much faster, almost instantly. The only key difference here is you have to manually copy the headers and start on row 2.
You first have to convert your Column G to an actual Date & Time Format using:
=ARRAYFORMULA(IF(B2:B="", "", DATE(LEFT(B2:B,4),MID(B2:B,5,2),RIGHT(B2:B,2))+(TIME(C2:C,D2:D,0))))
Then using Filter:
=FILTER(A1:G, G1:G < DATE(2022,8,24)+time(8,45,0))
Result:
Headers manually copied.
**Just added some test data to see that the filter works.
References:
Upvotes: 0
Reputation: 37155
I have made changes to formula to your G column =DATE(LEFT(B2,4),MID(B2,5,2),RIGHT(B2,2))+(TIME(C2,D2,0))
to make it pure datetime value. Then use below query formula.
=QUERY(A:G,"SELECT * WHERE G < datetime '2022-08-24 08:45:00'",1)
See your google sheet harun24hr
.
Upvotes: 1