DateTime with Rule Date filters does not work

Forum|Forum|10 years ago
May 6, 2016
14 replies
316 views

When using a DateTime field in filter criteria and applying a less than or equal to filter, the DateTime is not being resolved correctly in the query.

Example:

Filter Criteria

[i]
Rule results on test run
[i]
Rule Name: Admin: Load Post-Sales Relationships
Rule Criteria: CreatedDate >= 2016-05-05T07:00:00.000+0000 AND CreatedDate <= 2016-05-06T07:00:00.000+0000 AND Partner_Management_Status__c = 'Unmanaged' OR Deal_Type__c = 'Direct' AND Account_Name__r.JBCXM__CustomerInfo__c != null
Execution Time: 05/06/2016 12:00 PM

As you can see, the test was run at 12:00 PM (the org is in PST), so the UTC conversion for greater than or equal to CreatedDate is correct (7:00 UTC is 12:00 AM PST on the 5th). However, the second criteria for less than or equal to Rule Date is not resolving correctly. It says 05-06-2016 at 7:00 UTC again, which is 12:00 AM on the 6th. This is incorrect, as the operator was set to less than or equal to Rule Date, which means all of the 6th should be included (i.e DateTime should be 05-07-2016 at 6:59:59 UTC which would equate to 11:59:59 PM on May 6th PST).

EDIT:
Using Equals against a DateTime resolves to only records that were created at midnight, down to the millisecond, org time zone (CreatedDate = 2016-05-06T07:00:00.000+0000). This should equate to a DateTime range since these fields include time.
[i]

rules

Best answer by pavan_bandi

With Timezone standardisation in place, this problem is solved and these changes are part of SFDC 5.6 release.

Show previous replies

rbegleyAuthor

Helper ⭐️⭐️

Makes sense, thanks Sundar.

I think that most of the rules will still work, but I know there are situations where people, rather than doing less than or equal to Rule Date -1, would do less than Rule Date to avoid the bug. I think these scenarios will still function correctly with your proposed fix. I will brainstorm to see if I can think of any edge cases that might not port over correctly.

sundar

Expert ⭐️

Hi Everyone - I've made this post public now to get the opinion from Rules Engine Admins.
A subtle issue with Rule date which is actually working as dateTime instead of just date and also taking the scheduled time for the time component instead of Start / End of day. Proposed fix is explained in my comment above, let me know if this approach breaks anywhere.