In Dynamics CRM, how to reconcile dates entered by users in their local time zone with stored dates in UTC using API?

Question

I am writing code to integrate with Dynamics CRM, currently using the OrganizationService proxy and the Xrm Tooling SDK. We have many different users in different timezones using different instances of Dynamics CRM, and I am looking for a general solution.

The issue is one that has to do with the end user specifying a date in Dynamics and my integration code understanding the intent of that user at the time it reads that date using the service call. Note that I have no control over how my customers have set up their CRM instance, I just have read-access to their data.

The problem I am running into is that perhaps my customers want me to use columns they have designated for project start and end dates. For this example, let us say that the API "Logical Names" for these custom columns on the opportunity entity are custom_projectstartdate and custom_projectenddate. On their UI, they have presumably set up a date picker for their users and their users are choosing a date. And that date is one that their users are not thinking of as related to a particular time zone, but CRM will assume they are entering a date and time in their timezone, and will store that date and time as UTC in the underlying database.

At some point down the line, my integration code uses the OrganizationService to read that opportunity record and will retrieve those columns custom_projectstartdate and custom_projectenddate and will be delivered those dates as UTC. But really I need those dates as literals, as the actual date (irrespective of timezone) that the user entered, but I don't know what user entered those dates, or what timezone they were in at the time of their data entry. Or (for that matter) whether Daylight Savings Time was in effect when they entered those dates.

If the user who entered those dates happened to be in the GMT timezone (which is 0 offset from UTC when not in daylight savings time) then I should not offset those dates at all. On the other hand, if the user who entered those dates happened to be in Australia, I have a substantial offset to apply. But how can I know?

My suspicion is that I have to kick this problem back to my customer(s) and say that they need to figure out a way to get me string-literal dates that are irrespective of timezone, but I don't know enough about the administration of Dynamics CRM to suggest how they would do that. Suggestions for how to phrase that ask are welcome.

But maybe some kind StackOverflow commenter has a nifty solution whereby I can address this without involving my customer(s)? That would be preferable.

Answer 1

First of all sorry if I missed up some points in your question, it's quite long and more philosophical but I understand your struggle with Dynamics dates.

Inside Dynamics you can decide for a Date field how the value is stored, if "User Local" (meaning the value is always stored as UTC but is displayed converted to the local time of the user) or "Time-Zone Independent" (meaning the value is stored as UTC and is displayed to everybody as the UTC value).

From what you described you are in the first scenario.

Example: my time zone is UTC+8 and I created an appointment starting today at 9 AM. Dynamics will store the value as UTC (so the time stored is 1 AM UTC) and because the field is "User Local" I will see at 9 AM, a user with a time zone like UTC+1 will see it at 2 AM.

When you create a record by code usually (like when there is no impersonation) you run in a context of a specific user or an application user with a specific time zone defined in the settings (because in the end it's a user), but if you are using the C# SDK and you set a date field normally the value is stored as UTC (I wrote normally because in C# date values can be stored as Local, more info here https://docs.microsoft.com/en-us/dotnet/api/system.datetimekind?view=net-5.0)

If you are interested you can retrieve this information, you can find more details here https://docs.microsoft.com/en-us/dynamics365/customerengagement/on-premises/developer/sample-retrieve-time-zone-information (the samples are stored inside this GitHub repo that is linked https://github.com/Microsoft/PowerApps-Samples/tree/master/cds/orgsvc/C%23/RetrieveTimeZone)

For example you can create a dictionary with the User Id and the hours difference from UTC and use it to calculate the correct UTC value to insert when you set that specific field.

I hope this answers your question, feel free to comment if you have additional questions.