Solved

Date Comparison is not working correctly

Forum|Forum|1 year ago
July 14, 2025
7 replies
0 views

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

Best answer by shubhama926776

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.

mikes0011

Brainy

M

mohiniv1048Author

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

mikes0011

Brainy

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

shubhama926776Answer

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.

mikes0011

Brainy

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.

shubhama926776

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.

Sign up

Login with SSO

Login to the community

Login with SSO