Query to search a date in date range is not working

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

Parents Reply Children
  • Can you confirm you don't have the operators for "start" and "end" dates reversed?

    No

    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 

  • 0
    Certified Lead Developer
    in reply to shamima0001
    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.