Filter by Date Range

locomot1f

Greetings,

Wanted to put in a request to be able to add this kind of code to Sheetgo Filter Query:

select * where A > date '"&TEXT(DATEVALUE("1/10/2020"),"yyyy-mm-dd")&"'

I’m guessing because of the double quotes to add the function is what’s throwing it off at the moment, and giving the error that the literal date is the wrong format.

There’s plenty of documentation that shows this code does work in a google sheet query:

i.e.
=QUERY(A1:C17,"select * where A < date '"&TEXT(DATEVALUE("1/10/2020"),"yyyy-mm-dd")&"'")

thanks in advance.

talk soon.

Eugenia

locomot1f Hey there,

It’s actually possible to do that, but without the concatenation that you are using. This part of your clause is actually a sheets formula:
&TEXT(DATEVALUE("1/10/2020"),"yyyy-mm-dd")&

In order to do that inside the query you might have to do some casting just in case the engine gets confused. Try something like:
SELECT * WHERE TODATE(A) > DATE '2020-10-01'

Should work like a charm 😉

locomot1f

yes, I’ve gotten that to work

but what if you want the date to be automated to TODAY or another Column Value?

SELECT * WHERE A > DATE '"&TO_TEXT(TODAY(),"yyyy-MM-dd")&"'

Eugenia

locomot1f Indeed if its a date specified in a cell it won’t work. But you can definitely use the current date. The syntax is slightly different. Try this:
SELECT * WHERE A > TODATE(NOW())

And you can also use DateDiff for a particular interval:
SELECT * WHERE DATEDIFF(TODATE(NOW()),TODATE(A))<=365

This would bring us anything that happened in the last 365 days.

So maybe the request would be to have a filter that maps a particular cell in the spreadsheet. Maybe that would be the way to go.

locomot1f

Eugenia - i wanna jump through this terminal screen and give you a HUGE hug.

i could have sworn i tried the todate function before, but must have messed up the syntax.

that worked!!! and is exactly all I needed

Eugenia

locomot1f no worries. Indeed this subset of sql can be kinda finicky. Sometimes I have to keep switching between TODATE and DATE myself until I get it to behave properly.

Glad it worked.🏆