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!

Pingbacks and trackbacks (10)+

Comments are closed