Skip to main content
gaddamv0001
Known Participant
Oct 28, 2021 1:40 AM UTC
Question

filter values according to date from date and time field

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
    Oct 28, 2021 6:40 AM UTC

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

    gaddamv0001Author
    Known Participant
    Oct 28, 2021 6:50 AM UTC

    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
    Oct 28, 2021 7:13 AM UTC

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

    csteward
    csteward
    Brainy
    Oct 28, 2021 8:50 PM UTC

    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