Posted on 6. December 2011

Dynamics CRM DateTimes - the last word?

The subject of DateTimes in Dynamics CRM 2011 seems to always raises its ugly head on every project – I thought I'd *try* and create a guide for developers on future projects on how to deal with DateTimes in Dynamics CRM 2011.

Time Zones

Dynamics CRM stored Date/Time fields in the database as a SQL datetime field that is always converted to a UTC date. Each user has a Time Zone Code associated with their user settings. To list all the available TimeZoneCodes, you can use the following query against the MSCRM database:

SELECT TimeZoneCode, UserInterfaceName FROM TimeZoneDefinition order by UserInterfaceName

To list all user's selected Time Zone Code you can use:

Select SystemUserId, TimeZoneCode from UserSettings

There are a number of functions available in the MSCRM database that allow converting to and from UTC to local dates. The following function accepts a utc date and converts it to a local date based on the time zone code matching those in TimeZoneDefinition.

dbo.fn_UTCToTzCodeSpecificLocalTime(@utc,@timezonecode)

Dates are stored as UTC

Consider the following: Joe is in the New York office and creates an appointment in CRM with a scheduled start of '26 Nov 2001 13:00'. Karen is in the Paris office and opens up the same appointment created by Joe, and observes that the start time is '26 Nov 2011 19:00'.

  1. 26 Nov 2001 13:00 – value entered by Joe in New York office as scheduled start date/time. New York is in EST (UTC-5) – i.e. UTC minus 5 hours
  2. 26 Nov 2011 18:00 – value stored in the Database by CRM – converted to UTC date/time by adding 5 hours.
  3. 26 Nov 2011 19:00 – value viewed by Karen in the Paris office – CRM converts from UTC to Karen's local time of UTC+1 by adding 1 hour.

The following SQL shows this example in action:

PRINT 'Non-Daylight Saving Test'
DECLARE @utc datetime = '2001-11-26 18:00:00'

--(GMT-05:00)+1 Eastern Time (US & Canada) New York EST 
-- Entered as 2001-11-26 13:00:00
PRINT 'New York (GMT-05:00)+1	' + CONVERT(nvarchar(30),dbo.fn_UTCToTzCodeSpecificLocalTime(@utc,35),120)
PRINT 'UTC						' + CONVERT(nvarchar(30),@utc,120)
--(GMT+01:00) Brussels, Copenhagen, Madrid, Paris
PRINT 'Paris (GMT+1)+1			' + CONVERT(nvarchar(30),dbo.fn_UTCToTzCodeSpecificLocalTime(@utc,105),120)

Daylight Saving adjustments

The following scenario is similar to the above except, the date is now in the summer, and subject to daylight saving adjustments. Read this article for more info on daylight saving adjustments.

Joe is in the New York office and creates an appointment in CRM recorded as '26 June 2001 13:00'. Karen is in the Paris office and opens up the same appointment created by Joe, and observes that the start time is '26 June 2011 19:00'.

  1. 26 June 2001 13:00 – value entered by Joe in New York office as scheduled start date/time. New York is in EST (UTC-5) – but they are also on Daylight saving which is +1 hour.
  2. 26 June 2011 17:00 – value stored in the Database by CRM – converted to UTC date/time by adding 4 hours – less one hour due to the daylight saving.
  3. 26 June 2011 19:00 – value viewed by Karen in the Paris office – CRM converts from UTC to Karen's local time of UTC+1 by adding 1 hour and then another hour for daylight saving adjustment.

The important thing to understand is that daylight saving adjustments are based upon the date being entered, and not by the current date time at time of entry. So if a date of 26 June was entered in on the 26 November, the daylight saving adjustment would still be made. This ensures that datetimes are always constant in the same time zone – you wouldn't want the time of an appointment to change depending on what time of year you viewed the record.

The following SQL shows this example in action:

PRINT 'Daylight Saving Test'
DECLARE @utc datetime = '2001-06-26 17:00:00'

--(GMT-05:00)+1 Eastern Time (US & Canada) New York EST 
-- Entered as 2001-06-26 13:00:00
PRINT 'New York (GMT-05:00)+1	' + CONVERT(nvarchar(30),dbo.fn_UTCToTzCodeSpecificLocalTime(@utc,35),120)
PRINT 'UTC						' + CONVERT(nvarchar(30),@utc,120)
--(GMT+01:00) Brussels, Copenhagen, Madrid, Paris
PRINT 'Paris (GMT+1)+1			' + CONVERT(nvarchar(30),dbo.fn_UTCToTzCodeSpecificLocalTime(@utc,105),120)

Dynamics CRM *always* stores a time element with dates

Dynamics CRM doesn't support storing just dates, they will always have a time element even if it's not displayed in the User Interface or exports. This can cause issue for dates such as 'date of birth' – consider the following:

  1. 26 Nov 2011– Date of birth entered by Karen in the Paris office.
  2. 26 Nov 2011 00:00 - Date of birth sent to the Web Server by the form submit. Note that the time element is set to zero-hundred hours if a date time field is configured to only show the date element.
  3. 25 Nov 2011 23:00 - Date stored in the database converted to UTC by subtracting 1 hour – Since Karen's local time zone is in UTC+1.
  4. 25 Nov 2011 – Date shown to Bob who is in London on GTM (UTC+0)

So a date of birth entered correctly by Karen in Paris is showing as the wrong date to Bob in London due to the time zone UTC conversion.

The following SQL shows this example in action:

PRINT 'Date of birth test'
DECLARE @utc datetime = '2001-11-25 23:00:00'

--(GMT+01:00) Brussels, Copenhagen, Madrid, Paris
-- Entered as 2001-11-26 (sent as 2001-11-26 00:00:00)
PRINT 'Paris (GMT+1)			' + CONVERT(nvarchar(30),dbo.fn_UTCToTzCodeSpecificLocalTime(@utc,105),120)
PRINT 'UTC						' + CONVERT(nvarchar(30),@utc,120)
--(GMT-05:00)+1 Eastern Time (US & Canada) New York EST 
PRINT 'New York (GMT-05:00)	' + CONVERT(nvarchar(30),dbo.fn_UTCToTzCodeSpecificLocalTime(@utc,35),120)

Absolute Date solutions

There are a number of solutions to this absolute date issue:

  1. Adjust the date/time at point of entry (JavaScript or PlugIn) and convert to mid-day (12:00) so that any time conversion will not move it over the date line. This will only work if you don't have any offices that are more than 12 hours apart.
  2. Write a plugin that intercepts any Retrieve/RetrieveMultiple messages and adjust the time to correct for the time zone offset. This would only work when a date is displayed in a Form or Data Grid – it would not work with SQL based reports or when dates are compared within an advanced find search criteria.
  3. Store the date of birth as a string or 3 options sets for year, month and day – this is in fact the only way to completely avoid the time zone conversion issue for absolute date fields.

You can see the 12:00 date correction in action here:

  1. 26 Nov 2011– Date of birth entered by Karen in the Paris office.
  2. 26 Nov 2011 12:00 - Date of birth sent to the Web Server by the form submit (or adjusted in a PlugIn pipeline). 
  3. 26 Nov 2011 11:00 - Date stored in the database converted to UTC by subtracting 1 hour – Since Karen's local time zone is in UTC+1.
  4. 26 Nov 2011 – Date shown to Bob who is in London on GTM (UTC+0) Correct!
PRINT 'Date of birth test ( 12:00 corrected)'
DECLARE @utc datetime = '2001-11-26 11:00:00'

--(GMT+01:00) Brussels, Copenhagen, Madrid, Paris
-- Entered as 2001-11-26 (sent as 2001-11-26 12:00:00)
PRINT 'Paris (GMT+1)			' + CONVERT(nvarchar(30),dbo.fn_UTCToTzCodeSpecificLocalTime(@utc,105),120)
PRINT 'UTC						' + CONVERT(nvarchar(30),@utc,120)
--(GMT-05:00)+1 Eastern Time (US & Canada) New York EST 
PRINT 'New York (GMT-05:00)	' + CONVERT(nvarchar(30),dbo.fn_UTCToTzCodeSpecificLocalTime(@utc,35),120)

The downside of this is that if two offices are in time zones more than 12 hours apart, the conversion will still take the date over the date line, and will not show the birth date correclty. At this point, your only option is a text date field.

PRINT 'Date of birth test ( 12:00 corrected - timezone problem)'
DECLARE @utc datetime = '2001-11-26 17:00:00'

--(GMT-05:00)+1 Eastern Time (US & Canada) New York EST 
PRINT 'New York (GMT-05:00)		' + CONVERT(nvarchar(30),dbo.fn_UTCToTzCodeSpecificLocalTime(@utc,35),120)
PRINT 'UTC						' + CONVERT(nvarchar(30),@utc,120)
--Fiji (GMT+12)
PRINT 'Fiji (GMT+12)			' + CONVERT(nvarchar(30),dbo.fn_UTCToTzCodeSpecificLocalTime(@utc,285),120)

SDK Web Services DateTime Gotcha

The SDK Web Services can accept either a local datetime or a UTC datetime when performing a create/update, but will always return a UTC date on Retrieve/RetrieveMultiple. For this reason, you must be very careful if you retrieve a value, update it and then send it back.

Using the SOAP endpoint, you will always get a UTC date, to get into a local date on a client (assuming that the client has their datetime set correctly) use DateTime.ToLocalTime, but if you can't guarentee the time zone settings then use the LocalTimeFromUtcTimeRequest.

Contact contact = (from c in ctx.CreateQuery()
                where c.LastName == lastName
                    select c).FirstOrDefault();
Console.WriteLine("UTC Time " + contact.BirthDate.ToString());
Console.WriteLine("Local Time (Converted on Client) "  + contact.BirthDate.Value.ToLocalTime().ToString());

LocalTimeFromUtcTimeRequest convert = new LocalTimeFromUtcTimeRequest
{
    UtcTime = contact.BirthDate.Value,
    TimeZoneCode = 85 // Timezone of user
};
                    
LocalTimeFromUtcTimeResponse response = (LocalTimeFromUtcTimeResponse)_service.Execute(convert);
Console.WriteLine("Local Time (Converted on Server) " + response.LocalTime.ToString());

 

If you want to update the date, you need to ensure you specify if it's a local datetime or a UTC datetime.

newContact.BirthDate = new DateTime(2001, 06, 21, 0, 0, 0, DateTimeKind.Utc);
// or
newContact.BirthDate = new DateTime(2001, 06, 21, 0, 0, 0, DateTimeKind.Local);

 

If you are using the REST endpoint, then you would set a UTC date using the following format:

<d:BirthDate m:type="Edm.DateTime">2001-06-20T23:00:00Z</d:BirthDate>

 

To set a local date time, where it will be converted to UTC on the server simply omit the trailing Z. The 'Z' is from the Navy and Aviation's use of 'Zulu' time which is equivalent to UTC (but shorter!)

<d:BirthDate m:type="Edm.DateTime">2001-06-20T23:00:00Z</d:BirthDate>

GMT 'Time-bomb' Gotcha

The problem with being in the UK is that for half the year, the time zone is the same as UTC (GMT+0) – which means date time conversion issues are often not spotted if the development is taking place when British Summer Time (BST) Daylight saving is not in effect – this is because any dates entered into CRM are stored as UTC which is the same date – as soon as Daylight saving comes into effect, the problem is then spotted (hopefully!) because dates are an hour out in reports and integrations with other systems because the utc date is being read from the database or SDK and not converted to GMT.

Key Points:

So in summary, here are the key points to remember:

  1. Date/Times are always stored in the MSCRM database as UTC dates.
  2. When querying the Base table or views for an entity (e.g. ContactBase or Contact), the dates will be UTC.
    E.g. the following dates will be in UTC
    Select birthdate From ContactBase
    Select birthdate form Contact
  3. When query the Filtered Views, dates will be in the local time specified in the current user's settings. There is another field provided that is suffixed by UTC that provides the raw date without any convertion.
    E.g. The first date will be local time zone correct, and the second field will always be utc
    Select birthdate,birthdateutc from FilteredContact
  4. When sending a date/time in SOAP SDK Message (e.g. create/update), the date will default to local time if you use a DateTime.Parse – and if you want to send a UTC time, you must set the DateTimeKind to UTC.
  5. Important: When querying the SOAP SDK, any date/times will be returned as UTC dates, and must be converted to local time using DateTime.ToLocalTime if you know that the locale of the current process is set correctly, or the LocalTimeFromUtcTimeRequest SDK message.
  6. When importing &updating data via the Import Wizard, date/times must be specified in the local date of the user who is importing them.

With any luck, that should settle the matter!

Posted on 1. December 2011

The future of Silverlight for Dynamics CRM

Recently there has been much talk and rumours of Microsoft discontinuing Silverlight keeping in the tradition of speculating over the Flash vs. Silverlight vs. HTML 5 battle. The thing is; it is an imaginary struggle. It results from our deep seated need to keep things simple and 'black and white'. Sure it's fun to indulge in a bit of 'this house believes' style debating where you have to vote for or against; but I'm not doing that here.

Microsoft has been fairly clear in their direction of Silverlight over the last year; Silverlight is aimed primarily at 'Line of Business Applications' and 'Premium Media Experiences'. Perhaps one reason for there being so much speculation and confusion is down to the complex make-up of Silverlight. Unlike Macromedia's Flash it's not an all-or-nothing technology but rather a combination of many pivotal technologies such as XAML, the .NET Framework and the Command Language Runtime.

  • Rumour 1: There will be no Silverlight 6 – Does that matter? The CLR, .NET Framework and XAML are here to stay for a very long time. It could be that Silverlight will be 'merged' into Microsoft's new WinRT platform emerging from the Metro Design Language and Windows Phone 7 (WP7). I think I might start referring to Silverlight as 'XAML User Interface' until the future is cleared up.
  • Rumour 2: Support for browsers other than Internet Explorer will be dropped from Silverlight 5 – With Silverlight 5 RTW scheduled for the end of 2011, the beta does provide support for browsers other than IE. But from a Dynamics CRM perspective today, even if support for other browsers was dropped it wouldn't matter. After all, the Dynamics CRM connector for Microsoft Outlook doesn't run on other Operating Systems and the web client only supports Internet Explorer. There have been reports of future versions of Dynamics CRM running on other browsers in the 12+ month time frame. A re-write of this kind would be a huge and exciting investment from Microsoft. It will be interesting to see what other user interface enhancements will be made during the process; we might see full HTML 5 developer productivity tools for ISV client extensions. If this happens, then the story for XAML based client add-ins is somewhat diminished, but it doesn't go away, especially if you are targeting clients that you know will be running on a Microsoft Windows platform.
  • Rumour 3: Microsoft will stop producing hot-fixes and service packs for Silverlight - Microsoft are staying silent on the future of Silverlight at the moment, but simply taking the status-quo, we can expect mainstream support for at least 2 years after release date (taking us into 2014 for Silverlight 5), with extend support producing high priority security vulnerability fixes well beyond that. Microsoft has invested hard in dispelling their now out-of-date reputation of not taking security seriously, and they are not about to jeopardise that now. As far as the 'Premium Media Experiences', with truly mass-market companies such as Netflix and LoveFilm investing heavily in Silverlight, Microsoft will be forced to produce a clear migration path to what-ever the V.Next is going to be.

So back to reality; the huge productivity gains to be found from using Silverlight today as your user interface for Dynamics CRM 2011 cannot be ignored until HTML5 XRM developer tool support catches up. I challenge anyone to write a complex piece of user interface code in HTML 5 for Dynamics CRM 2011 in less time than it would take me in Silverlight!

So in summary, the reasons today to use Silverlight compared to ASPX pages are:

  • Silverlight can be seamlessly deployed as part of a CRM 2011 solution.
  • Authentication and Authorisation is seamless with Silverlight, where as ASPX pages need to be deployed as a separate web applicaiton using federated Claims Based security when integrating with CRM Online/IFD.
  • If you have Silverlight skills, it gives a much richer user interface in considerably less development time.
If the developer experience for HTML5 applications catch up with Silverlight, these points could easily apply as well.

Here is a little taster of a Dynamics CRM 2011 Ribbon Designer tool that will be released early next year – you've guessed it – written in Silverlight 5.

 

  •  

    Provided we write our apps with good separation of concerns using the MVVM/MVC paradigm, whatever the future holds, we'll be ready.