Hi,
I an trying to filter records from DB where selected date is between startDate & endDate in the query below but for some reason I am not getting any result, please help me with it.
Expression Rule:
a!localVariables( local!sampleData: a!queryEntity( entity: cons!DATA, query: a!query( logicalexpression: a!queryLogicalExpression( operator: "AND", filters: { a!queryFilter( field: "userid", operator: "=", value: ri!userid ), a!queryFilter( field: "startdatetime", operator: ">=", value: gmt(todate(ri!selectedDate)), applyWhen: not(isnull(ri!selectedDate)) ), a!queryFilter( field: "enddatetime", operator: "<=", value: gmt(todate(ri!selectedDate)), applyWhen: not(isnull(ri!selectedDate)) ) }, ignoreFiltersWithEmptyValues: true ), pagingInfo: a!pagingInfo( startIndex: 1, batchSize: - 1, sort: a!sortInfo(field: "createddate", ascending: true) ) ), fetchTotalCount: false ).data, { if( not(isnull(local!sampleData)), local!sampleData, {} ) } )
Discussion posts and replies are publicly visible
Can you confirm you don't have the operators for "start" and "end" dates reversed? Looking at the current state of your code it looks like for a date X, you're trying to query for an object where the Start Date is after X, and the End Date is before X. As far as I can tell that would cause the query to only return entries where the datetime is *exactly* X.
Mike Schmitt said:Can you confirm you don't have the operators for "start" and "end" dates reversed?
No
Mike Schmitt said:Looking at the current state of your code it looks like for a date X, you're trying to query for an object where the Start Date is after X, and the End Date is before X. As far as I can tell that would cause the query to only return entries where the datetime is *exactly* X.
But I am expecting all those records where SELECTED_DATE is greater-than-equal-to START_DATE and less-than-equal-to END_DATE
shamima0001 said:But I am expecting all those records where SELECTED_DATE is greater-than-equal-to START_DATE and less-than-equal-to END_DATE
Well, this means your operators are reversed.
The query filter operator of "<=", for example, should be read as "the DATABASE VALUE is LESS THAN OR EQUAL TO the INPUT VALUE". Your query as-written is basically assuming that this works the other way around, which is incorrect.
In other words you're trying to query a database entry where the entry's START_DATE is greater-or-equal to the input date, AND its END_DATE is less-or-equal to the input date. As I stated above, this would only work for entries that exactly match your input datetime (down to the second, or possibly millisecond, which may be difficult to do).
Switch the operators between your two query filters and see what happens.
Have tried converting the ri!selectedDate todatetime instead of todate? That might be causing issues in the gmt conversion and if the field you're searching is a datetime it appears. Another thing is you don't need the not(isnull(ri!selectedDate)) since you have the ignoreFiltersWithEmptyValues equal to true
I have modified filter operators and now the result looks correct, Except one issue.
If START_DATE is 02/23/2021 & END_DATE is 02/26/20021, and I am checking if the date 02/24/2021 exist in between STATRT_DATE & END_DATE the result is true and its true for 02/25/2021 & 02/26/2021 as well but if I am looking for 02/23/2021 it gives me false. Please have a look what's wrong with it, I have already checked it with todate() & todatetime() functions but no luck.
Expression Rule
a!queryEntity( entity: cons!DATA, query: a!query( logicalexpression: a!queryLogicalExpression( operator: "AND", filters: a!queryFilter( field: "userid", operator: "=", value: ri!userid ), logicalExpressions: a!queryLogicalExpression( operator: "AND", filters: { a!queryFilter( field: "startdatetime", operator: "<=", value: gmt(todate(ri!selecteddate)), applyWhen: not(isnull(ri!selecteddate)) ), a!queryFilter( field: "enddatetime", operator: ">=", value: gmt(todate(ri!selecteddate)), applyWhen: not(isnull(ri!selecteddate)) ) } ), ignoreFiltersWithEmptyValues: true ), pagingInfo: a!pagingInfo( startIndex: 1, batchSize: - 1, sort: a!sortInfo(field: "createddate", ascending: true) ) ), fetchTotalCount: false ).data
May I ask why you use gmt()?
Unknown said:May I ask why you use gmt()?
I guess dates are stored in GMT in DB and to compare dates from DB parameter dates should also be converted to GMT. Isn't it.
By default Appian stores all date and time values as GMT in DB. But it also translates this into the users time zone automatically. This means, that if you add another layer of translation to this, you might end up turning 02/23/2021 into 02/22/2021.
I strongly suggest to not do any timezone translations unless there is a very specific need.
When I try after removing GMT function it gives me an error,
Expression evaluation error at function a!queryEntity [line 4]: Cannot apply operator [LESS_EQUALS_THAN] to field [startdatetime] when comparing to value [TypedValue[it=7,v=2021-02-22]].
Sure, this is because the data types do not match, but gmt() turns your date into a dateTime. You cannot compare a date vs. a dateTime.