When a Date and Time field in Dynamics 365 is of format “Date Only” and it’s behavior is set to “User Local”, it can get really tricky when we want to use it’s date in our code to compare it to one another. The thing is that the value is always stored as UTC in the Dynamics 365 database.
If we select a date on the form, say 18/02/2020, this date will be nicely displayed on the form.
But when we look to the actual stored value, through the Web Api, we see that it is actually 17/02/2020 23:00
As you can see, Dynamics 365 also stores time regardless of whether it’s a Date Only field or not. And since we are currently in the UTC+1 Time Zone, this can cause some issues when using these dates in our plugins.
So how can we deal with this?
To make this generic, it’s best to retrieve the User Settings of the initiating user. Who knows, your user(s) might live in another time zone than where you developed and tested the functionality. And this way the time zone will always be correct.
First we need to retrieve the time zone code from the user settings:
const int TimezoneUtc = 85; //Setting a default, just in case Guid userId = _context.InitiatingUserId; Entity userSettings = _service.Retrieve("usersettings", userId, new ColumnSet("timezonecode")); int timeZoneCode = userSettings.GetAttributeValue<int?>("timezonecode") ?? TimezoneUtc; // 105 in our case
With this number, we can retrieve the standard name of the time zone. This is stored in the native timezonedefinition entity type of Dynamics 365:
QueryExpression query = new QueryExpression("timezonedefinition"); query.ColumnSet = new ColumnSet("standardname"); query.Criteria.AddCondition("timezonecode", ConditionOperator.Equal, timezoneCode); string timeZoneName = _service.RetrieveMultiple(query).Entities.Select(t => t.GetAttributeValue<string>("standardname")).FirstOrDefault(); // "Romance Standard Time" for time zone Code 105
Next step is to find the corresponding time zone based on it’s standard name. For this we use the built-in TimeZoneInfo class of C#:
TimeZoneInfo timeZoneInfo = TimeZoneInfo.FindSystemTimeZoneById(timeZoneName);
And finally, we can easily convert the UTC DateTimeStamp to a user local DateTimeStamp:
DateTime completedOn = task.GetAttributeValue<DateTime>("thr_taskcompletedon"); DateTime completedDate = TimeZoneInfo.ConvertTimeFromUtc(completedOn, timeZoneInfo); // 17/02/2020 23:00 will be converted to 18/02/2020 00:00