The Problem
[Dataverse does now provide date only fields, refer to https://docs.microsoft.com/en-us/power-apps/maker/data-platform/behavior-format-date-time-field]
Dynamics CRM does not provide date only fields. Although date fields may be configured to show the date only, internally the time is stored as well. The time that is stored is usually the time in UTC that corresponds to midnight in the user’s time zone. This may cause date fields to show different values depending on the time zone of the user.
Consider a system with users in two zones, GMT (0 hours offser) and PST (-8 hours offset). If a user in the GMT time zone enters 12th December in a date only field, the stored date and time is 12th December 00:00. The time difference between GMT and UTC is 0 hours and no change is made to the time.
When the date field is viewed on a form or list it is shown according to the user’s time zone. When a user in the GMT time zone views the field the correct day is shown; there is no difference between GMT and UTC so no adjustment is made to the time.
But when a user in the PST time zone (-8 hours offset) views the field, 8 hours is subtracted from the time making the value of the field 11th December 16:00 and so the user sees the day as 11th December instead of 12th December.
If all users in a CRM system are in the same time zone there is no issue.
A Solution
To get around this problem, a solution is to add an OnChange script to date fields in forms to set the time to noon (midday) UTC. This should mean that for time zones with offsets of between -12 and +12 hours the date shown to the user (with their time zone adjustments) is still the same as the date entered by a user.
But just adding 12 hours to the time is not sufficient because the date and time are always stored in UTC and so the user’s time zone must be considered when trying to set the time to 12:00 noon.
So looking at the PDT example again, a date of 12th December is stored as 12th December 08:00. This is midnight + the 8 hours time zone difference for the user. The trick here is to make a call to the user settings and get the timezonebias setting (which is stored in minutes) and subtract that from the time. So to get the time to midday the calculation is:
- Midnight + 12 hours – (480/60) 8 hours (to remove the 480 minutes PDT time zone difference added by CRM)
Limitations
This technique might not work when daylight savings are observered at different times across time zones and it almost certainly will not work for time zones greater than +/- 12 hours. There are time zones with offsets greater than +12 hours (see http://en.wikipedia.org/wiki/List_of_time_zones_by_UTC_offset).
Also, this solution will not work where dates are are entered in records by means other than forms such as workflows or imports. For these situations, you will need to write a plug-in to modify the time part of the date.