Error Comparing User Local Date Time Fields with Date Only Format in Dynamics 365

Sean Roque, 22 November 2019

We encountered a situation when comparing Date Time fields in Date Only format in Dynamics. For a particular Calculated Two Options field, the condition is checking whether one Date Time field is greater than the other

In situations where the Expiry Date is the same as the End Date, most of the time the Two Options field is set correctly. However, there are instances where it will show the opposite value i.e. End Date and Expiry Date are the same, but the Two Options field is set to No.

This is occurring despite the Format being set to Date Only.

What we have found is that it is important to set the Behavior to Date Only as well. What this does is it sets the Time value in the system as 12:00 a.m. which can be kept consistent if the same setting is applied to all Date Only fields within an entity for comparison. Details can be found here.

If the Behavior is set to User Local for example, there will be inconsistencies in the Time value. Below is a screenshot of FetchXML results for a Date field with Format as Date Only and Behavior as User Local.

What about instances where the User Local behaviour is a requirement and must be preserved?

In cases like these, consider programmatically populating your Calculated Field instead. Compare the two Date Time fields by taking the .Date component, which will return 12:00 a.m. as the Time value.