C#, .Net, Sql Server, Salesforce, Dynamics CRM etc,
Charity and Not-for-Profit sector

Thursday, April 18, 2013

Dynamics CRM UTC dates

A quick post:

Lets say you're in the UK and you have Dynamics CRM Online, and you extract the data to a local Sql Server database with Scribe Online RS. When you look at the Contact.Birthdate column in the local database, you'll find that some birthdates are as you expected:

1984-04-03 00:00:00.000

But some have an hour subtracted from them, making them 23:00 the day before:

1984-04-02 23:00:00.000

This, of course, is happening because Dynamics CRM stores all datetimes internally as UTC datetimes. The Dynamics front-end converts them to your local timezone so you never notice. But if you push or pull dates in or out using the Dynamics API then you have to make sure you're using UTC. Scribe RS uses the API so it too extracts dates in their UTC format.

UTC generally matches the UK's timezone except for summer months when the UK switches to daylight savings time. If you look at your dates of birth in your local DB, you'll see a pattern: The subtraction of one hour only happens to dates between the end of March and the end of October.

Unfortunately it seems that SQL Server on its own has no ready-made way to convert a UTC datetime into a local datetime, but C# does.