Date Only fields display different dates for users in different Time Zones

If you have CRM users in multiple time zones and happen to use “Date Only”  fields in CRM, such as for Birthdays, Contract Dates, etc. –You may want to ensure that a date set by a user in one time zone is displayed correctly to users in time zones west of them.

When a Date Only field is populated, CRM actually still stores the date with a time of midnight in the users’s local time zone.

When a user in New York saves the date 1/1/2010 in CRM, it is stored as Midnight, January 1^st Eastern Time. – However when a user in Los Angeles views the record, the date time is adjusted to display in Pacific Time and the LA user sees 12/31/2009!  

This is because [1/1/2010 00:00:00 AM] in New York is the same moment as [12/31/2009 09:00:00 PM] in Los Angeles. 

To adjust for this apparent discrepancy, I set the unseen time portion of a date-only value to Noon local time. – By doing so, users in other time zones will see the same date displayed in CRM.

For Example If I wanted to ensure that the Birthday of a Contact if set by a user in New York will be displayed correctly for a user in Los Angeles, I add the following script to the ‘on-change’ event of the contacts’ birthday to set the time value of the date to Noon.

 

When the user updates the birthdate field, the time portion is set to noon allowing other users to see the same date – even after it’s adjusted to their own time zone.

(By the way, the timezone effect isn't apparent if the user in in the western timezone sets the date field, since Midnight Tuesday in LA is still also Tuesday in New York. (Tuesday 3AM Eastern Time) )

VERY IMPORTANT for users in world wide deployments:  This tip is for implementations where all users are within 12 hours of each other and all on the same side of the International Date Line.  (e.g. all users are in US or all in Europe, etc.)  Because any given moment of the day is recognized as 2 different dates around the world, no date/time setting will allow users in every time zone to see the same date when that date is adjusted to their local time zone. – In cases where you need a consistent date displayed in a world-wide deployment, use a text field – even though you will lose CRM’s time comparison features in Advanced Find, you are able to control the displayed date for all users.

Scott Sewell

Comments

[Christian]

I am glad, that somebody sees this as well as a problem. I've been in different discussions with Microsoft and they are claiming, that it is used as designed. As we are in working in different time zones around the globe and do have a lot of date only fields, we do often have the problem. I am claiming this as a bug. I am seeing different work arounds, but everyone of those has got its own implications and shortcomings.

[Scott Sewell]

Christian - Thanks for the feedback.

I see where you've added your suggestion to the microsoft connect suggestion box - (I didn't see it earlier) - I've added a comment and validated that it happens for me too -

Hopefully others who have experienced this, will chime in and raise the profile of this item.

(https://connect.microsoft.com/dynamicssuggestions/feedback/ViewFeedback.aspx?FeedbackID=471412)

[David Withers]

wow I had to use a trigger to get around this, I know unsupported. Now I can remove the trigger. What's even more funny is I just posted this on the forums a few days ago. I love your blog!

[Chris Condron]

Wouldn't setting the time to noon GMT fix thew problem?
i.e. for someone in New York set it to 7 am (because EST is GMT -5)
That way when you cross the date line you also cross midnight making it work in New York & Sydney.
-Chris

[Scott Sewell]

Chris - you're correct that setting to noon GMT would minimize the confusion - The problem would still exist for one timezone in the pacific, but it's a safe bet I won't have users there. :)

The effort here was trying the least-complex workaround for updating lots of fields across a couple of dozen entities in use. (I know the time could be adjusted via triggers/direct SQL but didn't want to go down that path.) - And unfortunately I wasn't able to find a way to determine the user's GMT off-set in the script.

I'm always looking for better ways to solve the problem - How did you approach it?

[John Hoven]

To do this by script and get the user's GMT off-set in script you'll need to extract the Date portion of the date time. Then make a call to the CRM Web Service (LocalTimeFromutcTime) passing in the date the user entered as:
yyyy-MM-DDT112:00:00 Z

This way the web service will transform from 12pm UTC into the user's local time and it uses the often hotfixed and maintained versions of the CRM timezone definitions to do the transformation.

Of course this only is set if the value is set by the CRM form (not web service calls and workflows). What we do is something similar to http://www.patrickverbeeten.com/pages/ContentItemPage.aspx?id=12&item=86&p=true which takes any date on a create or update and replaces it with a 12 Noon UTC value. It ended up that all we needed was a slightly modified version of the Create/Update Pre plugin.

[John Hoven]

We decided to host our plugin on codeplex for anyone else who is interested.

http://mhdateplugin.codeplex.com/