Solved
DateTime with Rule Date filters does not work
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]
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]
Best answer by pavan_bandi
With Timezone standardisation in place, this problem is solved and these changes are part of SFDC 5.6 release.
View originalReply
Sign up
If you ever had a profile with us, there's no need to create another one.
Don't worry if your email address has since changed, or you can't remember your login, just let us know at community@gainsight.com and we'll help you get started from where you left.
Else, please continue with the registration below.
Welcome to the Gainsight Community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.