Date Comparison is not working correctly

Certified Associate Developer

I am having a column  named due On in the record with data type as date and time,In db the data type for this field is timestamp.I have to add a filter ,having two options one Before or at 7:59 PM Today and After  7:59 PM Today and display this filter and read only grid in a interface
This is the filter code I am writing currently,but it is not working correctly kindly help me regarding this

a!recordFilterList(
name: "SLA Due Date",
options: {
a!recordFilterListOption(
id: "before",
name: "Before or at 7:59 PM Today",
filter: a!queryFilter(
field:'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{56f4ac00-4b4b-4c1b-b841-1938bd23ac80}dueOn',
operator: "<=",
value: rule!NWS_SlaCutOffTime(),



)
),
a!recordFilterListOption(
id: "after",
name: "After 7:59 PM Today",
filter: a!queryFilter(
field:'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{56f4ac00-4b4b-4c1b-b841-1938bd23ac80}dueOn',
operator: ">",
value:rule!NWS_SlaCutOffTime(),
)
)
},
allowMultipleSelections: false
)

NWS_SlaCutOffTime(),having code below


datetime(

year(today()),
month(today()),
day(today()),
19,59,00
),

  Discussion posts and replies are publicly visible

  • 0
    Certified Associate Developer

    a!recordFilterList(
      name: "SLA Due Date",
      options: {
        a!recordFilterListOption(
          id: "before",
          name: "Before or at 7:59 PM Today",
          filter: a!queryFilter(
            field:'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{56f4ac00-4b4b-4c1b-b841-1938bd23ac80}dueOn',
            operator: "<=",
            value:  rule!NWS_SlaCutOffTime(),
             
              
            
          )
        ),
        a!recordFilterListOption(
          id: "after",
          name: "After 7:59 PM Today",
          filter: a!queryFilter(
            field:'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{56f4ac00-4b4b-4c1b-b841-1938bd23ac80}dueOn',
            operator: ">",
            value:rule!NWS_SlaCutOffTime(),
          )
        )
      },
      allowMultipleSelections: false
    )
    


    NWS_SlaCutOffTime(),having code below

     datetime(
       year(today()),
       month(today()),
       day(today()),
       19,59,00
     ),

  • 0
    Certified Lead Developer
    in reply to mohiniv1048

    and can you clarify exactly on what you mean when you say "not working correctly"?

    1. what have you tried
    2. what result did you expect
    3. what result did you see instead
  • +1
    Certified Lead Developer

    I am assuming Timestamp precision mismatch - database records include seconds/milliseconds but your cutoff time is exactly 7:59:00, causing records like 7:59:30 PM to incorrectly fall into "After 7:59 PM".

     Use 8:00 PM as your cutoff instead of 7:59 PM, and use < and >= operators instead of <= and >. 

    Update your NWS_SlaCutOffTime() rule:

    /* Use 8:00 PM for cleaner boundary logic */
    datetime(
      year(today()),
      month(today()),
      day(today()),
      20, 0, 0
    )


    Update your filter logic:

    a!recordFilterList(
      name: "SLA Due Date",
      options: {
        a!recordFilterListOption(
          id: "before",
          name: "Before or at 7:59 PM Today", 
          filter: a!queryFilter(
            field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{56f4ac00-4b4b-4c1b-b841-1938bd23ac80}dueOn',
            operator: "<",
            value: rule!NWS_SlaCutOffTime()
          )
        ),
        a!recordFilterListOption(
          id: "after", 
          name: "After 7:59 PM Today",
          filter: a!queryFilter(
            field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{56f4ac00-4b4b-4c1b-b841-1938bd23ac80}dueOn',
            operator: ">=", 
            value: rule!NWS_SlaCutOffTime()
          )
        )
      },
      allowMultipleSelections: false
    )


    Let me know if your problem is different.

  • +1
    Certified Lead Developer
    in reply to Shubham Aware

    I also agree with this, but as OP did not really specify what they meant by "not working", it's hard to know whether this is even vaguely the direction of their issue.  But this is good general guidance for implementing timestamp-based filters.

  • +1
    Certified Lead Developer
    in reply to Shubham Aware

    Consider to use userdatetime() instead of datetime() to avoid issues with timezone hickups.

  • 0
    Certified Lead Developer
    in reply to Mike Schmitt

    True, without seeing the actual behavior it's hard to pinpoint the exact issue. The timestamp precision and timezone considerations are the most common culprits for date/time filter problems, so starting with these solutions usually resolves most cases.