The Microsoft Dynamics CRM Blog
News and views from the Microsoft Dynamics CRM Team

Date/Time Values in Microsoft Dynamics CRM

Date/Time Values in Microsoft Dynamics CRM

  • Comments 7

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.

 

Ronghua Jin

  • Great post, but speaking of dates and time. How come that CrmDateTime values are formatted as strings and not DateTime data structures inside the Web Service API?

  • One of the biggeswt problems I have faced is not all users in a time zone set their time zone correctly!!!!.  If you then use Javascript to perform data and time calculations on the on-load or on-save events you can get very strange results between users, and your time calculations will be incorrect.

    I would like to see a way of being able to set users time zones from within settings>bu>users.  I would also like to see a simple way of returning a users time zone information to form and field events.

  • Managing resources is one of the critical activities in running your business. And one of the critical

  • Hi there,

      I wonder how I can get a current date without time on the mail merge.  

      So far, I have tried to use all the datetime attributes from the data fields and even created one my own with just date only.  But, none of them can display the correct date I want.  They display like either the wrong time with time or nothing.  

      Is there anyone can help me with that issue?  Thank you very much.  

  • differende between two dates with time

  • Any ideas on how to update a datetime field in c# using reflection?

    Here is my code:

    DateTime dateTime = DateTime.Parse("12/12/2009");

    CrmDateTime crmDateTime = new CrmDateTime();

    crmDateTime.date = dateTime.ToString("M/d/yyyy");

    // assume crmContact

    Type t = crmContact.GetType();

    t.GetProperty("mydatefield").SetValue(crmContact, crmDateTime, null);

    The field is nullified, instead of the date being saved. I'm not sure of another way to set datetimes using a dynamic field.

    Any hints?

  • Hello,

    Tha is right it would be great that the time zone follow the BU address, as the default one, and the user could overwrite it if necessary.

Page 1 of 1 (7 items)
Leave a Comment
  • Please add 6 and 4 and type the answer here:
  • Post