Inherent to its nature as a Relationship Management product, Microsoft Dynamics CRM manages the time for all resources of a business as an important part of the system. Managing time values in the CRM system appears to be simple, but in practice it is a complex task, just as it is in real life managing time is not a trivial task. In this article, I will introduce you to the design decisions we made regarding time value management in Microsoft Dynamics CRM, the reasons for the design decisions and some of the consequences you need to be aware in order to best use the system.
If you stop and think for a minute about a time value, one of its characteristics is that it is a relative value, not absolute when we deal with a time value. That is, a date time value has a time zone. There is a way to express time as an absolute value. This is usually referred to as the Universal Time Coordinated (UTC). Converting a local time value in a given time zone to UTC requires a time conversion rule for that time zone. In the simplest of use cases where a Microsoft Dynamics CRM customer only deals with date time values in a single time zone, a date time value is almost as simple as managing an integer value. But most enterprise customers deal with date time values in multiple time zones. And in some cases, the UTC time conversion rule for a particular time zone also changes as a result of legislations, e.g. the daylight time starts earlier and ends later in US starting in 2007.
The requirement to deal with these use cases in managing time for resources in a business presents some challenges. The nature of time management is to best utilize resources and that is to find the best time to meet a customer’s requirement. That is scheduling. A frequent operation during scheduling is to compare available time with requested time. That is when dealing with date time values in multiple time zone or multiple UTC conversion rules become a more complex task than dealing with integers.
The first requirement is to make sure that date time values from all inputs are comparable. The design decision is to convert date time input values into common time zone date time value for comparison. An event at 9:00am in Eastern Time (US & Canada) happens before an event at a 9:00am Pacific Time (US & Canada). And we know that because we compare them by converting the user input value into a date time value in the same time zone, e.g. UTC time value.
The second requirement is to make sure that date time value comparisons are fast. It is important since Microsoft Dynamics CRM customers manage millions of records and a performing system is mandatory. The design decision is to take the hit and do conversion of user date time at input and output time and do most operations and store date time values in UTC time zone value in the system. If you have not checked, schedule a task at certain time, and check the ScheduledStart value in the Task table in SQL. You will find that the ScheduledStart value is the UTC time of the time you set in the UI. At the same time, we also need to know the local time zone that needs to be used when displaying the date time value to the users. This is the TimeZoneCode in the UserSettings table or the Time Zone setting in the Set Personal Options dialog (CRM tool bar -> Tools -> Options …).
So that is the design of date time values in Microsoft Dynamics CRM. One of the consequences is that a date time value in the future will be converted to UTC value according to the UTC time conversion rule as we know it today. However, the time conversion rule can change as it is a matter of legislations which do change. When that happens, the stored UTC value will not be correct as the user might originally intended, which is expressed in local time. That is the reason for what you might have gone through with the 2007 DST patch.
We are working on improvements to the Microsoft Dynamics CRM product, including date time value management to make it a better product for our customers. We welcome your feedback as you are using it.
I hope you enjoyed reading this and have a better understanding of the design and usage of date time values in the Microsoft Dynamics CRM. I plan to do similar treatment for other aspects of the scheduling and service management features in the future if you find this to be useful.