Solved

Query to search a date in date range is not working

Forum|Forum|5 years ago
February 22, 2021
13 replies
0 views

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

Best answer by stefanhelzle0001

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?

mikes0011

Brainy

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.

The Expression Guru

S

shamima0001Author

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

mikes0011

Brainy

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.

The Expression Guru

M

michaelb0014

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

S

shamima0001Author

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

stefanhelzle0001

Brainy

May I ask why you use gmt()?

Sign up

Login with SSO

Login to the community

Login with SSO