Query to search a date in date range is not working

Question

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,
 {}
 )
 }
)

Stefan Helzle · Accepted Answer

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?

michaelb0014 · Answer

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

Mike Schmitt · Answer

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 · Answer

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.

Query to search a date in date range is not working

Top Replies