When was the last time you had a “behavior” problem with the date field in Microsoft Dynamics CRM? Well, for many of us the answer might be “recently”. Before the introduction of the “behavior” modifier for date fields, things could get a little confusing and problematic in certain situations. Let’s look at how to improve behavior of the date field in Microsoft Dynamics CRM.
Let’s say that you are integrating Invoice information from your ERP system into CRM. You created an ‘Invoice Date’ date field in Microsoft Dynamics CRM (date only) to hold the ERP Invoice Date. You are on the East Coast and the integration user also has an Eastern Time zone assignment. When you look at the date on an Invoice, it matches ERP – all good, right?
Ball of Confusion
If you are in a time zone west of the Eastern zone and you look at that ‘Invoice Date’ in CRM, you see that it is one day prior to what the East Coast people see. What??
Many of you know that ‘date only’ fields are stored in SQL Server with a time stamp as well as the date. Even though you see ‘June 1, 2016’ on your form, the date is really stored as ’12:00 am on June 1, 2016’. CRM tries to keep us honest by converting date/time info based on the users Local time zone, so if you are a West Coast user then what you see is the date that results from subtracting 3 hours from the East Coast ’12:00 am on June 1, 2016’ date/time – meaning you’re going to see May 31, 2016 instead of June 1, 2016. In this case, honesty is not always the best policy!
We’ve worked around this issue by forcing the addition of 12 or some other number of hours to the date so the effect of time zone changes could be neutralized. But who wants to do that for a bunch of fields?
Microsoft to the rescue! As of CRM Online 2015 Update 1, we can now specify the behavior of date fields. Select ‘Time-Zone Independent’ and all users see the same date information regardless of their Local time zone – NICE! No manual data updates needed!
Know that once you change a field to the ‘Time-Zone Independent’ behavior, you cannot change it back to something else. And because, as it says below, some date and time values may change, you should check to see if some of your other handiwork like business rules and workflows will be affected by the data change.
By John Clifton, Microsoft Dynamics CRM Consultant with xRM³, a Microsoft partner specializing in Dynamics CRM consulting, implementation, integration and Microsoft Dynamics CRM administrative services. Based in San Diego County Southern California.