Bringing Dates Back to the Future! Migrating Dates from CRM 4.0 to Dynamics 365

Nick Chin, 20 June 2019

Recently I was migrating Contact data from CRM 4 to Dynamics 365 and came across a Date Time issue. All the dates were correct besides the Date of Birth.

Why is only the Date of Birth incorrect?

This was because in CRM 4 the Date Time fields were set to "User Local", meaning the date is displayed in the user’s local time zone.

CRM 2015 introduced the option for “Behaviour”, this allowed Dates to have 3 options:

•    User Local
•    Date Only
•    Time-Zone Independent

Now in CRM 2015 or later the Date of Birth has its behaviour set to “Date Only”, this means the same date is displayed for all time zones.

Note: that this problem will occur for any date fields that were originally “User Local” and now have a behaviour of "Date Only" or “Time Zone Independent”.

The full Dynamics 365 “Date Only” description: “  When the behaviour of a field is “Date Only,” field values are displayed with no time zone conversion. The date portion of the value is stored and retrieved as specified in the UI and SDK. The time portion of the value will always be 12:00 A.M. The behaviour of this field can’t be changed once it’s saved.”

How do I fix the Date?

For the Data Migration I used SSIS (SQL Server Integration Services) and retrieved the dates as UTC, therefore I needed to convert the Date of Birth or any other Date field that was no longer “User Local” to my local time zone.

In SSIS this can be done using a "Derived Column Transformation".

For this I replaced the birthdate field with an Expression that checks if the field has data, then adds hours to the birthdate to convert to my Time Zone.

I am located in New Zealand, so my UTC Offset is currently UTC + 12, this is New Zealand Standard Time (NZST).

For countries with day light saving/summer time you will need to adjust your query.

Dates with a format of "Date Only" will store all datetimes as 12 a.m. in the user’s time. Normally for NZST converted to UTC it would be 12pm the previous day, however for some reason the dates in this database were 11 am or 12 pm, so make sure to check all the "time" for the dates stored.

The easiest way to convert this was to add 13 hours to all dates, because the Date of Birth field is Date Only, therefore Dynamics 365 will convert the date to 12 a.m. automatically.

Tip:
Make sure the source user and destination user have the same Time Zone set in CRM, this will make it easier to validate the data.