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)
CodePudding user response:
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 -
CodePudding user response:
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
.
CodePudding user response:
Suggestion:
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: