Skip to main content
G
gaddamv0001
October 28, 2021
Question

filter values according to date from date and time field

  • October 28, 2021
  • 9 replies
  • 0 views

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.

    9 replies

    selvakumark
    selvakumark
    Participating Frequently
    October 28, 2021

    Hi,

    This might help you, but please do test it out thoroughly:

    a!queryLogicalExpression(
  operator: "AND",
  filters: {
    a!queryFilter(
      field: "bookingDate",
      operator: ">=",
      value: todatetime(today())
    ),
    a!queryFilter(
      field: "bookingDate",
      operator: "<",
      value: todatetime(today() + 1)
    )
  }
)

    G
    gaddamv0001Author
    October 28, 2021

    Thank you for your reply. I actually tried in this way but it give me the data only for today, but i want like:  whichever date I click/select i need to get that day all booking details

    selvakumark
    selvakumark
    Participating Frequently
    October 28, 2021

    Yes, instead of today() function, if you pass your respective variable, it will work as expected.

    csteward
    csteward
    October 28, 2021

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

    Terms & ConditionsAccessibility statement