June 19, 2012 – On vacation at the Jersey shore, so just as soon as I got done hanging out with Snookie & the crew, what else would a diligent CRM consultant do besides update his time zone in the various Dynamics CRM organizations he belongs to? Normally based in Chicagoland (GMT -6), I opened the Personal Options dialog to reset my time zone to GMT -5:
The next thing I did was relax a little by updating the birthdays of a few of my contacts who were born on the summer solstice:
A little later I signed in with a different account (the one with ClickDimensions permissions, so I could do my annual Happy Summer Solstice Birthday email blast) and happened to select the same view of contacts. Here’s what I saw:
That’s strange, I thought. I knew I’d just entered them with 6/21 birthdays. What’s going on? So the next thing I did was customize the form to show date and time for the birthday field. (By default it shows date only, but you can change it by customizing the form, double-clicking on the field, clicking the Details tab and then clicking Edit.) After saving, publishing and refreshing, here’s what I saw on one of the contact forms:
Doing a little envelope-back date math, it became obvious what was happening: I’d entered these birthdays as my GMT -5 time zone user, and was now viewing them as my GMT -6 time zone user. When it’s midnight on the east coast, it’s 11 PM in Chicagoland, right? It turns out that Dynamics CRM always stores date fields with both the date and the time, but when you set the Format property to Date Only date values are entered with a default time value of midnight, in the time zone of the user entering the record. At midnight June 21 on the east coast (GMT -5) it’s 11 PM June 20 in Chicago (GMT -6), so these date only date fields will always appear as a day earlier to the Chicago user.
Strange, right? When I first learned about this I was surprised I hadn’t encountered it before and that more clients hadn’t asked about it. I suspect it’s because many people either a) don’t notice it, or b) shrug their shoulders and move on to more important issues.
But for me this is a critically important issue: how embarrassing would it be to build an incorrect marketing list for my Summer Solstice email blast?
There’s a simple fix for this problem, and since I’m vacation that’s what I’m going for. Create a Jscript web resource with the following code:
Then customize the form, double-click the date field in question and click the Events tab.
First you need to add the library containing the function as a Form Library. Then you need to add a function call to the SetDateToMidDay function. In this example, I want the OnChange event of the Birthday field to call the function, so it looks like this:
From that dialog, clicking the Edit button with the function selected gives you the following dialog; the key thing here is to pass the special context parameter so your code can be generic, always knowing which control called it:
The code is simple: it checks for date values with a time of 0 (midnight), and changes them to noon. As long as your “earliest time zone user” is within 12 hours of the latest, this will show date only fields with the display date entered by the user, regardless of the time zone of the user viewing them.
And remember: this applies to all date fields, regardless of whether they’re formatted as Date Only, or as Date and Time. So even if you don’t use email blasts to wish contacts happy birthday, it may still be an issue. For example, many organizations customize fields such as the Due Date field on the Task entity (schema name: actualend) so that it displays as Date Only. This will cause the same problems we saw above, but in a possibly more problematic way: a task entered by an east coast user with a due date of June 21 will appear to a west coast user with a due date of June 20.
As I said, this fix is simple, and like a lot of simple fixes, it’s not foolproof. More complete solutions are implied by the discussions on several good articles, including this article by Frank Lee, and this article on the Dynamics CRM team blog.
Maybe someday we’ll be able to do date arithmetic in workflow and dialog processes. That would be a pretty nice enhancement, wouldn’t it? If that gets added at some point, I’ll update my book to include it. In the meantime, if you want to find out what you can do now using Dynamics CRM workflows and dialogs, check out my new book at http://www.CRMBizBook.com
Cheers – and Happy Solstice!