Date/Time Values in Microsoft Dynamics CRM

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

Comments (11)

  1. 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?

  2. Andy Milner says:

    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.

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

  4. Sabrina says:

    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.  

  5. says:

    differende between two dates with time

  6. gcanivet says:

    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(); = 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?

  7. DLD says:


    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.

  8. Subbu says:

    Why do CRM dates have no milliseconds? Createdon, modifiedon dates have no milliseconds. in MS CRM DateTime is a day + hh:mm:ss.  No milliseconds. Is there is a reason fo this.

  9. <a href="">jithin</a> says:

    What's weird is that the web UI returns the dates correctly, whereas querying using SQL server and XRM return them incorrectly.

    The time settings on the server machine are correct, I've installed rollup 16, is there anything else I can try?

  10. Jai B says:

    B4 u read following. Pls hv luk @ "UserSettings","TimeZoneDefinition","TimeZoneRule"views,View defn of "FilteredCampaign" for say "ActualStart" Date, fn_UTCToTzSpecificLocalTime and fn_UTCToTzCodeSpecificLocalTime.

    I would like to understand the effect of timezone rule record on already created record of UserSetting.

    Suppose, we have a user U1 belonging to timezone code say 65. When user was created,(say in 2006), timezone RULE "r1" was in effect.So,in usersetting U1 record got created with timezonecode = 65 and all remaining (offset columns) values filled w.r.t. "R1".

    Please correct me, if this understanding is correct. Now, when 2010 came, R2 became effective for timezone code 65.

    My first basic question is – does user U1 record gets updated for with offset values belonging to R2?

    1. If yes, i.e. U1 gets updated with R2. So, now, dates in any record created in any entity say campaign which belong to period between 2006 to 2009 will now be read w.r.t. R2 but not R1. (WHICH I FEEL IS WRONG, if so please PLEASE EXPLAIN THE CORRECT BEHAVIOUR). Also, Dates greater than 2010 will also be read w.r.t. R2. (which is correct)

    2. If no, i.e. UI record does not get updated with R2. Now, if another user created in Feb-2010, which rule would be taken into consideration? R1 OR R2? I suspect R2. So, now there are 2 users belonging to same timezone code but with different rules (different values in offset columns). Is it ok to have 2 user with different Rules in single Timezone Code?

    My focus to talk about usersetting is because FilteredViews read usersetting rules but not timezonerule. Expected process should have been to hit timezone rule table via (usersetting to TimeZoneDefinition to TimeZoneRule) from FilteredCampaign. As reading rule from UserSettings does not question the rule in effect, it simply convert date in UTC to rule specified in UserSetting.

    The function that the filtered views use is  – fn_UTCToTzSpecificLocalTime. In my opinion it should have used fn_UTCToTzCodeSpecificLocalTime, as it takes date in UTC, timezone code and convert date into local time zone by applying proper "RULE".

    Please help me to understand why FilteredView refer Usersetting but not timezonerule.

    Secondly, when form is rendered- To which it refers to read date?- a) FilteredView, indirectly UserSetting or b) TimezoneRule with the help of fn_UTCToTzCodeSpecificLocalTime(i.e. some equivalent CRM code).

    In case "b)", please justify why filtered views are asked to use while SSRS reporting. As this can result in mismatch in dates on UI and on report.

    In case "a)", then why concept TimeZoneRule when CRM itself uses FilteredViews, which indirectly hit UserSettings.

    Is the role of timezonerule only while creating user? Or also usersetting gets updated automatically when new rule is in effect.

    Sole reason to go in such depth is to show correct date on SSRS report which is exactly same as on CRM Form UI. & to understand how timezone rule works.

  11. Unknown says:

    Interesting to see the background of how the WRONG decision was made all those years ago 😀