DATE-TIME Values in Microsoft Dynamics

In Microsoft Dynamics D365 date values can be stored in three different ways. These ways can be set by selecting the behavior of the datetime field.

When you are creating the datetime field, you need to consider what is the business requirements for that particular date field. Let’s think that If the organization is a world-wide organization and each user can be in a different time zone than you need to consider to use User Local behavior in your date fields. User Local datetime fields store the data in Dynamics DB as a GMT converted date format. You can see the examples below. In this example a user who is in GMT+2 Timezone creates these records.

When creating the datetime field, you need to consider what the business requirements are for that particular date field. In case the organization is a global business where each user can be in a different time zone, you need to consider using “User Local” behavior in your date fields. “User Local”datetime fields store the data in the Dynamics database as a GMT converted date format. You can see the examples below. In this example a user who is in GMT+2 Timezone creates these records.

D365 displays this date according to the user’s local time and converts the date appropriately. This is really good if you want to achieve a global timezone conversion for each and every user around the world without manual calculations. It’s also really important that the “User Local” type is used in cases where hours are significantly important in that datetime field. If the granularity of the date field does not require hour calculations, then we should select the “Date Only” type.

“Date Only” Behavior is simple. We do not need the time to be stored in that date format and only the date component is important. When you insert a value in this format, it is the exact same date for every user. One important thing to remember is that when you create a datetime field with “Date Only” behavior you cannot change the behavior in the future. 

The third and last behavior is the “Time Zone Independent” behavior. The name is self explanatory where the date is stored with date and time but there is no time zone conversion. Like “Date Only”, this behavior can not be changed after it is set.

So it is possible to modify the behavior from “User Local” to “Date Only”. You can have multiple reasons for wanting to do that. One of the issues that you can encounter is SQL reports that directly work on D365 Database tables. Because of the fact that “User Local” datetime fields are stored in the database with GMT file format, it is possible to get wrong dates if you do not properly convert the timezone in your SQL statements. Also if you do not need the time for that particular date field there will be redundant data in D365.

So, successfully converting a “User Local” behavior to “Date Only” requires a configuration change in Dynamics but also a small development effort to convert existing records with that field. Configuration change is simple; just go to the datetime field  properties, change the behavior, save and publish.

This change will ensure the new records that are created will have their dates in “Date Only” format but what about the existing records in the database? Yes we need a conversion. 

Luckily Microsoft has a built-in request function called ConvertDateAndTimeBehaviorRequest which we can use in order to set the dates if we change the behavior of the datetime field. Sample code can be found below.

ConvertDateAndTimeBehaviorRequest request = new ConvertDateAndTimeBehaviorRequest()

{

Attributes = new EntityAttributeCollection()

{ new KeyValuePair<string, StringCollection>(“account”, new StringCollection() { “new_sampledatetimeattribute” }) },

ConversionRule = DateTimeBehaviorConversionRule.SpecificTimeZone.Value,

TimeZoneCode = 110,

AutoConvert = false // Conversion must be done using ConversionRule };

// Execute the request

ConvertDateAndTimeBehaviorResponse response =(ConvertDateAndTimeBehaviorResponse)_serviceProxy.Execute(request);

In this example we are changing the datetime behavior in account entity’s new_sampledatetimeattribute column. ConversionRule aaplies the rule of the conversion Timezonecode is the timezone that the date needs to be converted (ex. 110 for Sweden)

If you want more details on this subject, Microsoft has a good in depth documentation which you can find via the following links:

https://docs.microsoft.com/en-us/dotnet/api/microsoft.xrm.sdk.messages.convertdateandtimebehaviorrequest?view=dynamics-general-ce-9

HTTPS://DOCS.MICROSOFT.COM/EN-US/DYNAMICS365/CUSTOMERENGAGEMENT/ON-PREMISES/DEVELOPER/BEHAVIOR-FORMAT-DATE-TIME-ATTRIBUTE?VIEW=OP-9-1#CONVERT-BEHAVIOR-OF-EXISTING-DATE-AND-TIME-VALUES-IN-THE-DATABASE

HTTPS://DOCS.MICROSOFT.COM/EN-US/POWERAPPS/MAKER/DATA-PLATFORM/BEHAVIOR-FORMAT-DATE-TIME-FIELD