DateTime is Tricky
I got to have a little fun today and I’d like to share that with y’all. I know a few techies that read this that might find the following entertaining.
I got an instant message from my company’s president today asking if I knew C#. I respond in the affirmative. Seems a company that has forwarded us a little bit of business in the past months has ran into a problem and wanted a little help. One of their developers was struggling a bit with some date issues in an application. I got the guy’s phone number and went to helping him out.
Here’s the scenario: They’re using Microsoft CRM (never touched it before in my life) and they’re trying to take a date field off one form, add a year to that, and store that as a field related to another form. Simple enough. But here’s the problem: User enters 1/1/2008 into the form, form is submitted, one year is added to the date and it comes back as 12/31/2008 on the next screen. The developer I’m talking with seems convinced that he’s just not using the .Net libraries improperly, or maybe there’s some quirk to them.
I have three ideas pop into my head:
1) Some yahoo has tried doing date calculations with string manipulation functions. A common mistake.
2) The 2007 daylight savings change mandated by the United States Government might be playing games with a pre-2007 date when you add a year to it. Yes, blaming the government is something I try to do even in software development.
3) It’s just your generic timezone problem.
Well, the code was clean (ie: not written by a moron unaware of the date library functions in .Net) and it wasn’t dealing with pre-2007 dates, so that left option #3.
First things first: I change his code from using .AddMonths(12) to .AddYears(1) to rule out any obvious quirks in the .Net framework. Same results.
We dive into the database behind the CRM and discover that 12/31/2008 date is actually 1/1/2009 in the database. We did deeper and find that the originally entered 1/1/2008 appears in the database as 1/1/2008 8:00am.
“Where is this server located?” I ask. “Tahoe (Nevada).” is the reply. Everything comes together.
What’s happening is MS CRM is taking the input date of 1/1/2008, which really means 1/1/2008 00:00am, and converting that to UTC before it stores it in the database. UTC is another name for GMT, Greenwich Mean Time, for those of you, like me, that learned about timezones before the UTC name was created. They differ slightly in technical terms but they’re close enough for the purposes at hand.
Now, when the code executes to retrieve that date and add one year to it it’s using MS CRM to get that value, and the CRM software is translating it back to local (Pacific) time as it does that. So, what is 1/1/2008 8:00am becomes 1/1/2008 00:00am, one year is added to that, making 1/1/2009 00:00am, and that value is rammed back into the DB without going through the CRM’s business layer.
Consequently, when you use the CRM software to pull back that 1/1/2009 00:00am value it rips 8 hours off that and you get 12/31/2008 4:00pm. Everything now comes full circle.
I show the developer where and how to turn that localized time back into UTC before he runs his computations on it and this fixes the error. The result is that we compute the 1 year addition on UTC time instead of the localized time so that when CRM pulls back the computed date it translates 1/1/2009 8:00am to 1/1/2009 00:00am and the display is correct.
Easy as 3.14159 when you know what’s actually going on.