Date Comparison is not working correctly

Question

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

Shubham Aware · Accepted Answer

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.

Mike Schmitt · Answer

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.

Stefan Helzle · Answer

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

Mike Schmitt · Answer

and can you clarify exactly on what you mean when you say "not working correctly"? 
 
 what have you tried 
 what result did you expect 
 what result did you see instead

Date Comparison is not working correctly

Top Replies