Hello,
I have a problem with date filter in a query,
This is the code:
filters: { a!queryFilter( field: "fechaVencimiento", operator:">", value: toDate(now()) ),
fechaVencimiento is date of BBDD.
The problem is that when I execute the query it is returning the last two records, while I should not return anything because those records have the current date but not greater.
Do you know any solution for this?
Regards, and thank you very much
Discussion posts and replies are publicly visible
I would suggest you try it without "toDate()" -- either try just "now()", or try "today()" to return a datetime value with just the date (i believe either of these should work for your filter though).
If that still doesn't seem to work right, you might want to check that the CDT used by the Data Store Entity you're querying from has the "fechaVencimiento" column declared as the correct data type (i.e. that it's date, and not text or something else incorrect).
sandrap845 Please verify the time zone as well, for example, you can try gmt(todatetime(today())) to get the proper result.
gmt(todatetime(today())) to get the proper result.
Hello Mike,
fechaVencimiento in CDT is DATE.
if I put what you told me about now () or today () without the hit () it returns the following error:
you can think of another alternative?
Hello Malay Doshi:
The test returns the following error, yo creo que es porque fechaVencimiento es de tipo DATE.If I put the toDate with what you were telling me, it doesn't make a mistake but I get records today
Hi Sandra,
Try to use the function today() instead of now(), because your datatype is a date and if you use a todate(now()) this will return to date and time.
Regards
Hello Karina,
The problem is when the current day is the same as the date if the fechaVencimiento is higher or lower does the checking, the thing is that I want to make the filter for the dates greater than the current day
Hi Sandrap845,
toDate(now()) - This value actually contains date with timestamp. You can check the timestamp by using the below expression:
datetext(todate(now()), "z")
Can you try the below expression and see what is the date returned:
date(year(now()), month(now()), day(now()))
Then you can change your query filter as below and see how it worksa!queryFilter(field: "fechaVencimiento",operator:">",value: date(year(now()), month(now()), day(now())))
Hope this helps
My first instinct is to suggest you keep hunting for any issues with your particular configuration, because I've done this many times and it's always worked without much trouble.
Hi Sandra
Did you use the query editor? maybe with this functionality could help you to test your query and find the problem
https://docs.appian.com/suite/help/19.2/using-the-query-editor.html#create-rule-inputs
All right Mike, I finally deleted the CDT and generating it again worked