Sonoma Partners Microsoft CRM and Salesforce Blog

Migrating and Integrating Dates to Microsoft Dynamics

Today's blog post was written by Rob Jasinski, Principal Developer at Sonoma Partners.

Whether migrating data into CRM from a legacy system or from an other external source or handling an ongoing integration sync into your CRM environment, handling dates can pose unique challenges. In CRM, dates are actually stored as date and time, even though there may be no time portion to the date. If no time is entered or supplied, it is defaulted to 00:00 (midnight). Also, the time portion is stored as UTC in CRM. When migrating data into CRM, the time zone of the user that is migrating (or integrating) the data is used.

So, for example, if the user is in the Central time zone, and a date of “10/15/2016” is passed into a field called birthdate, first “00:00” time is added to the date, then CRM converts the date and time to UTC. So it finally is stored as “10/15/2016 06:00” (+6 hours for CST) in CRM. Even though the time portion may never be displayed to the user, CRM is still always converting this date and time to the user viewing the data’s native time zone. Now if a user in the Mountain time zone is displayed this date, they will actually see “10/14/2016” in CRM. The reason for this is the date was migrated with no time, so it defaulted to midnight CST (since the user used to migrate the data was in the Central time zone). Internally, CRM then converts this to “10/14/2016 11:00pm” for the users in the Mountain time zone and since time may not be displayed, the user may only see the date portion, “10/14/2016”. This can be confusing and ultimately, the wrong date is displayed to the user.

There a couple of workarounds for this. First, instead of using a date field, use a text field in CRM and store the date as text. The problem with this is the date cannot be used in any date calculations or native CRM date filtering logic. The other workaround is to take the time portion and change it to 12:00 (noon). So in the example above, modify the date to “10/15/2016 12:00” before passing it into CRM. Once CRM converts this date to UTC, it will still allow for a 12-hour leeway, in either direction, for time zone conversions. So, in the example from earlier, a date of “10/15/2016 12:00” would be stored as “10/15/2016 18:00” in CRM. This date, displayed to a user in the Mountain time zone, would convert to “10/15/2016 11:00” thereby preserving the actual correct date once displayed.

If the time portion is required (i.e. for appointments, transactions, auditing, etc.) or if your CRM implementation is global, then this work around will not be the right solution. But if the time data is needed, then this is usually not an issue since the time portion would most likely be displayed to the user anyway.

Ease into the cloud with Microsoft Dynamics Lifecycle Services

Topics: Microsoft Dynamics 365