Query to search a date in date range is not working

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.

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 !

Thanks It worked !