Hi All,
I have stored date and time in DB and need a query expression to filter based on two dates range?
Thanks,
Deepak
Discussion posts and replies are publicly visible
hi deepak,
1.) check
docs.appian.com/.../fnc_system_a_query.html
https://docs.appian.com/suite/help/20.4/fnc_system_a_querylogicalexpression.html
https://docs.appian.com/suite/help/20.4/fnc_system_a_queryfilter.html
2.) i suggest a query logical expression with an AND operator and 2 or 4 query filters.version with 4 filters: you can use filter with ">=" for each smaller start point and "<=" for each datetime end point you are looking for. version with 2 filter: i am not 100% sure for the "between" operator. but the first version should do the trick
Below, is Error for DateTime Field(requestedDate) and ri!startDate(12/22/2020) and ri!EndDate(12/30/2020) is Date Picker input
Expression evaluation error at function a!queryEntity: Cannot apply operator [LESS_EQUALS_THAN] to field [requestedDate] when comparing to value [TypedValue[it=7,v=2020-12-22]].
a!queryEntity( entity: cons!PTS_TICKET_TEST, query: a!query( logicalExpression: a!queryLogicalExpression( operator: "AND", filters:{ a!queryFilter( field: "requestedDate", operator: "<=", value: ri!StartDate ), a!queryFilter( field: "requestedDate", operator: ">=", value:ri!EndDate ) } ), pagingInfo: a!pagingInfo(1,- 1) )).data
Hi depaak,
this message is perhaps a bit confusiong, but check your formats.-> you have to compare datetime fields to datetime fields. and datefields to datefields.This means: if your "requestedDate" is a dateTime format then ri!StartDate has to be in a datetime format as well. then the operators should work.
Hi Richard,
Thanks for information! It is working with date time format