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
When I use todatetime() instead of gmt like this todatetime(local!selectedDate) then it gives me result like it was previously with gmt but not the once one which I am expecting.
"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."
OK. I made up my own example code here. My user time zone is UTC+1 (Germany). My values in DB are like 2/23/2021 1:31 PM. Now when I search for todatetime(2/23/2021) = 2/23/2021 0:00 PM, then this does not work obviously.
What you might want to search for is something like: Give me any date time which is before the end of a certain day.
Translated into Appian: datetime(year(ri!selecteddate), month(ri!selecteddate), day(ri!selecteddate), 23, 59, 59)
But only for the startdate.
Questions?
Thanks It worked !