Solved

DateTime with Rule Date filters does not work

rbegley
Rank
Badge
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]

icon

Best answer by pavan_bandi 16 August 2018, 14:15

View original

14 replies

josh_white
"Less than or equal to Rule Date" excluding records from today seems like unexpected behavior from the user's perspective.



For filters on a DateTime field, it would be nice if we were able to either:



1) Have an option to tell the rules engine to treat the value as a Date. From a SOQL perspective, possibly using the DAY_ONLY() function detailed here.



or 2) Have an extra parameter to choose the time we want to filter against. So in Rob's example, he could say "Created Date <= Rule Date, 24:00".
rbegley
Rank
Badge
This is extremely important, can someone please review.
rbegley
Rank
Badge
Still open.
sundar
Rank
Userlevel 5
Hey Rob - Was thinking of removing the option of Equals & Not Equals when a date time field is selected. Is that sane?
rbegley
Rank
Badge
That would be a place to start, but we also need to make less than or equal to function correctly (equate to the last second of the day rather than the first).
elizabeth
Is there an update on this?
dstokowski
Rank
Badge +3
Sundar - Let's add this to our top list for Rules Engine and Reports filtering changes as this has been a challenge for a long time (and still causing escalations and loss of confidence with customers).  The services team is doing workarounds in MDA (creating truncated date only fields to avoid this confusion) that is completely solvable within our product
dstokowski
Rank
Badge +3
Discussed with Sundar and Murthy.  Proposal is to address this - pending timing confirmation from Sundar this week (week of 10/31) and may be able to include in a patch.
rbegley
Rank
Badge
What is the proposed fix? There are hundreds of rules built off of DateTime fields that I am sure have been configured "incorrectly" in an effort to circumvent this issue.
sundar
Rank
Userlevel 5
Thanks for the heads up Rob. My guess is end of Nov or First week of Dec. 



What breaks now? 

Rule is scheduled to run at 09:00 AM. There is a filter condition for Created DateTime > (Subtract 1 day from Rule date) AND Created Date <= Rule date. This filters the data that is created b/w 09:01 AM previous day and 9:00 AM of Today (Rule date) 

Proposed Fix :  Honor simple english

Created date < Rule date 12:00AM

Created date >= Rule date 12:00 AM

Created date > Rule date 11:59 PM

Created date <= Rule date 11:59 PM



Also operate in the timezone that is set in Rules Engine.
rbegley
Rank
Badge
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
Rank
Userlevel 5
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.
dstokowski
Rank
Badge +3
This is the comment Sundar is referring to that describes the proposed logic.
pavan_bandi
Rank
Badge
With Timezone standardisation in place, this problem is solved and these changes are part of SFDC 5.6 release.

Reply


Terms & ConditionsCookie settings