Dataverse: Update Time zone adjustment from Time zone Independent to User Local and Date time Behavior

Hello everyone! I’m sharing my observation regarding Dataverse Date time (again). Previously, I updated several DateTime attributes from Time zone adjustment “User Local” (which is the default value) to “Time zone independent“. Then, my functional reported that the Date Time seemed weird as the Created On of the record vs the “Time zone independent” value has a gap and requested me to update again to “User Local“. But, from UI operation being blocked (I believe this couldn’t be done as MS doesn’t want to deal with data issues). But, before we go to how to change back, I also want to share things I learned about DateTime.

Once set as “Time zone independent“, we can’t set it back to “User Local“

First, I created the below table (as you can see, I have User Local Date, TZ Independent Date, User Local Date Only, and TZ Independent Date Only) and inserted the data from UI:

Data from UI vs SQL 4 CDS by Mark Carrington

As you can see, the TZ Independent Date-related values will be saved into DB without converting to UTC.

BTW, In SQL 4 CDS, we also have a feature to show the DateTime as UTC times/Local times (by default will be “UTC times”):

SQL 4 CDS setting to Treat date/time value as UTC times/Local times

And, for example, you have a Console application/WebAPI that will deal with inserting/updating data. You need to make sure to always set the Date as your current user TimeZone and convert it to UTC Time (in the below code, I’m using Betim Beja’s method that you can check here):

var userId = new Guid("F9E7905C-CF7A-EE11-8179-002248201668");
var userTimeZone = TimeZoneInfo.FindSystemTimeZoneById(TimeZoneDefinitionQueries.GetTimezoneByUserId(userId, service));

var result = service.Retrieve("tmy_transaction", new Guid("2ce5c827-af14-ef11-9f89-7c1e5218d596"), new ColumnSet("tmy_userlocaldate", "tmy_tzindependentdate", "tmy_userlocaldateonly", "tmy_tzindependentdateonly"));

var updated = new Entity("tmy_transaction", result.Id);
updated["tmy_userlocaldate"] = DateTime.UtcNow.AddHours(-4); // around 5 AM
updated["tmy_tzindependentdate"] = userTimeZone.ConvertToLocal(DateTime.UtcNow).AddHours(-4); // around 5 AM and using DateTime.Now as my local value same with my settings
updated["tmy_userlocaldateonly"] = userTimeZone.ConvertToLocal(DateTime.UtcNow).Date.ToUniversalTime();
updated["tmy_tzindependentdateonly"] = userTimeZone.ConvertToLocal(DateTime.UtcNow).Date;
service.Update(updated);

And here is the result from UI and SQL 4 CDS looks like:

UI vs SQL 4 CDS

Back to the main topic of how to set the “Time zone independent” to “User Local“. FYI, this operation modifies the Customizations XML directly, it is not an official way to do it (do it at your own risk). First, you need to export the Unmanaged solution of your Dataverse table. Once you get the .zip, extract the zip and you can inspect the XML.

In the below screenshot, I’m comparing the DateTime “User Local” vs “Independent Time zone“. As you can see, the difference is just in the tag “Behavior” whose values of 1 being “User Local” and 3 being “Time zone independent“.

User Local vs Time zone independent

So, to change the behavior back to “User Local“, you just need to change the value from 3 and set it to 1. Once done, you can zip and import it to your Dev environment > Publish All Customizations.

Here is the result of it:

TZ Independent Date updated to “User Local”

And if you are wondering what it looks like in the UI, here is the result of the existing data:

Result

Hope you are learning something today! Happy CRM-ing!