Posts Tagged ‘time zone’

[D365 CE] Migrate data to field with “Time Zone” format

December 14, 2018 Leave a comment

Have you ever worked with “Decimal” field with “Time Zone” Format?

Lets assume your Dynamics application has a global presence and you need to capture “Customer” info along with their “Time Zone”, we can create a Decimal field with “Format” set as “Time Zone”.


With “Time Zone” format, even though, you create a field of “Decimal” type, in UI it will render as drop down and in ‘Advanced Find’ you get a string.


Lets see how to migrate data to the “Time Zone” format field.

Whats “Time Zone” format:

As you would have noticed in above screens, “Decimal” field rendered as Drop down with all time zone options. Now the question is where we are getting these option?

The answer is, Dynamics has an internal entity named “Timezonedefinition” which pulls the time zone options from.

“Timezonedefinition” entity will have below 2 fields

  • Standardname – Name of the Time Zone
  • Timezonecode – Time zone code which is a decimal value

When you add a ‘Decimal’ field with ‘Time Zone’ format to an entity, in UI (i.e., Forms, Advanced Find, Views, etc.) you would get the ‘Standardname’ (i.e., Time zone name) however in the back end ‘Timezonecode’ gets saved.


To check that, export the records to Excel and you would find decimal values in “Timezone” field.


As you notice above, “(GMT-10:00) Hawaii” field rendered as 2 in exported excel, which “Timezonecode”

How to migrate data to “Time Zone” format field:

  • First you need to fetch the “Time zone codes” using “RetrieveMultiple” SDK call (Sample code below)

var request = new RetrieveMultipleRequest{
Query = new QueryExpression(“timezonedefinition”){
ColumnSet = new ColumnSet(“timezonecode”, “standardname”),

var response = (RetrieveMultipleResponse)_service.Execute(request);

foreach (var item in response.EntityCollection.Entities){
var timeZone = (Entity)item;Console.WriteLine(String.Concat(“Time zone name: “, timeZone[“standardname“], “, time zone code: “, timeZone[“timezonecode“]));

  • Executing above code would fetch “Timezone Names” along with “Timezonecode”


  • Once you have the codes, you can migrate the records by setting the “Time zone codes” to “Time zone format” field.
  • In the excel file, add new records with “Timezonecode”


  • After the successful excel import you should see new records in UI with “Time Zone Name” replaced with “Time zone code” passed from excel.






Fixing time zone issues while reading datetime fields using jscript in CRM 2011

When you read datetime field values using OData or SOAP , you may not get exact datetime values if your CRM application is using by users from different time zones.

We can fix the problem by¬†using the ‚Äútimezonebias‚ÄĚ field from current users ‚ÄúUserSettings‚Äú

timezonebias & timezonedaylightbias are system-calculated fields based on the time zone of current user

Follow below steps to fix the problem

  • Read the ‚Äútimezonebias‚ÄĚ &‚Äútimezonedaylightbias‚ÄĚ ¬†field by querying ¬†‚ÄúUserSettings‚Äú by current user id
  • Get your datetime field and substract the ‚Äútimezonebias & timezonedaylightbias‚ÄĚ fields to get exact value

Below is the script for the above steps

  • Read Current UserSettings

function RetrieveUserSettings(callBackFunction) {

try {

var serverUrl = Xrm.Page.context.getServerUrl();

var oDataEndpointUrl = serverUrl + “/XRMServices/2011/OrganizationData.svc/”;

//get current user id from context

var UserID = Xrm.Page.context.getUserId();

var RetrieveUserSetting = new XMLHttpRequest();“GET”, oDataEndpointUrl + “/UserSettingsSet(guid'” + UserID + “‘)”, true);

RetrieveUserSetting.setRequestHeader(“Accept”, “application/json”);

RetrieveUserSetting.setRequestHeader(“Content-Type”, “application/json; charset=utf-8”);

RetrieveUserSetting.onreadystatechange = function () {




} catch (e) {

alert(“Error on RetrieveUserSettings method”);



  • Call the above function to get ‚Äútimezonebias & timezonedaylightbias‚ÄĚ fields


  • Read the “timezonebias” & “timezonedaylightbias”¬†field from Call Back function and substract it from your datatime field value

   function RetrieveUserSettingCallBack(retrievedUserSetting) {

try {

if (retrievedUserSetting.readyState == 4) {

if (retrievedUserSetting.status == 200) {

var retrievedUser = this.parent.JSON.parse(retrievedUserSetting.responseText).d;

// Assume you got datetime field to below object

var dateValue;

var actMinutes = dateValue.getMinutes();

alert(“Skewed datetime – ” + dateValue);¬†

if (userSettings.TimeZoneBias != null) { 
            dateValue.setMinutes(userSettings.TimeZoneBias * -1); 

if (userSettings.TimeZoneDaylightBias != null) { 
            dateValue.setMinutes(userSettings.TimeZoneDaylightBias * -1); 
// Add the actual minutes


alert(“Exact datetime – ” + dateValue);



} catch (e) {




Here is the article on handling the time zone differences  in Plug-ins