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
  • 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.