filter values according to date from date and time field

gaddamv0001

A Score Level 1

over 2 years ago

I have a requirement where i need to filter values according to date but the field in CDT is given as date and time field.

For Eg: bookingDate in CDT is the date and time field. But, when I filter the data in the expression rule , I need to get data based on booking date only not time. ( For eg: I need to get the number of people who booked the tickets for the day but i dont want according to date and time I just want the count for the particular entire day). Can anyone please help me to solve this!

Thank you in advance.

Discussion posts and replies are publicly visible

Parents

0 Chris over 2 years ago

Just to add, my preference to convert date values to datetime for this type of scenario is with fn!datetime(), as todatetime() applies a timezone offset:

a!localVariables(
  local!bookingDate: today(),
  local!pagingInfo: a!pagingInfo(1,-1),
  
  a!queryEntity(
    entity: cons!YOUR_DSE,
    query: a!query(
      paginginfo: local!pagingInfo,
      logicalexpression: a!queryLogicalExpression(
        operator: "AND",
        filters: {
          a!queryFilter(
            field: "bookingDate",
            operator: ">=",
            value: datetime(
              year(local!bookingDate),
              month(local!bookingDate),
              day(local!bookingDate),
              0,
              0,
              0
            )
          ),
          a!queryFilter(
            field: "bookingDate",
            operator: "<",
            value: datetime(
              year(local!bookingDate),
              month(local!bookingDate),
              day(local!bookingDate)+1,
              0,
              0,
              0
            )
          )
        }
      )
    )
  )
)

Reply

0 Chris over 2 years ago

Just to add, my preference to convert date values to datetime for this type of scenario is with fn!datetime(), as todatetime() applies a timezone offset:

a!localVariables(
  local!bookingDate: today(),
  local!pagingInfo: a!pagingInfo(1,-1),
  
  a!queryEntity(
    entity: cons!YOUR_DSE,
    query: a!query(
      paginginfo: local!pagingInfo,
      logicalexpression: a!queryLogicalExpression(
        operator: "AND",
        filters: {
          a!queryFilter(
            field: "bookingDate",
            operator: ">=",
            value: datetime(
              year(local!bookingDate),
              month(local!bookingDate),
              day(local!bookingDate),
              0,
              0,
              0
            )
          ),
          a!queryFilter(
            field: "bookingDate",
            operator: "<",
            value: datetime(
              year(local!bookingDate),
              month(local!bookingDate),
              day(local!bookingDate)+1,
              0,
              0,
              0
            )
          )
        }
      )
    )
  )
)

Children

No Data