Behavior and format of the date and time attribute

If you have users and offices around the world, it is important to properly represent date and time values in multiple time zones. The DateTimeAttributeMetadata (DateTimeAttributeMetadata EntityType or DateTimeAttributeMetadata class) is used to define and manage attributes of type DateTime in Dynamics 365 Customer Engagement (on-premises). Use the DateTimeBehavior property (For Organization Service see, DateTimeAttributeMetadata.DateTimeBehavior) to define whether to store date and time values with or without time zone information, and use the DateTimeAttributeMetadata.Format property to specify the display format of these attributes.

You can also use the customization area in Customer Engagement to define the behavior and format of the date and time attributes. More information: Behavior and format of the Date and Time field.

Note

All date and time attributes in CRM Online 2015 Update 1 and Dynamics CRM 2016 (on-premises) or later now support values as early as 1/1/1753 12:00 AM.

For Dynamics 365 Customer Engagement (on-premises) versions earlier than CRM Online 2015 Update 1 and Dynamics CRM 2016 (on-premises), you cannot define the behavior of the date and time values. By default, the date and time values are stored as UserLocal behavior as described later in this topic.

 

Specify the behavior of a date and time attribute

You can use the DateTimeBehavior (DateTimeBehavior ComplexType or DateTimeBehavior class) to specify a value for the DateTimeAttributeMetadata EntityType.DateTimeBehavior property. The DateTimeBehavior contains the following members; each member returns a string with the same value as the member name:

Member name and value	Description
UserLocal	- Stores the date and time value as UTC value in the system. - The retrieve operation returns the UTC value. - The update operation converts the UTC value to the current user’s time zone value, and then stores the updated value as is or as the equivalent UTC value depending on the kind (DateTimeKind) of the value specified for update. If the specified value is of UTC kind, it’s stored as is. Otherwise, the UTC-equivalent value is stored. - Retrieving the formatted value converts from UTC to the user’s current time zone based on the time zone and locale setting of the user. - For the Web API, the attribute is exposed as DateTimeOffset. - This behavior is used for system attributes like CreatedOn and ModifiedOn, and cannot be changed. You should use this behavior for custom attributes where you want to store date and time values with the time zone information.
DateOnly	- Stores the actual date value with the time value as 12:00 AM (00:00:00) in the system. - For the retrieve and update operations, no time zone conversion is performed, and the time value is always 12 AM (00:00:00). - Retrieving the formatted value displays the date value without any time zone conversion. - For the Web API, the attribute is exposed as Date. - This behavior should be used for custom attributes that store birthdays and anniversaries, where the time information is not required.
TimeZoneIndependent	- Stores the actual date and time values in the system regardless of the user time zone. - For the retrieve and update operations, no time zone conversion is performed, and actual date and time values are returned and updated respectively in the system regardless of the user time zone. - Retrieving the formatted value displays the date and time value (without any time zone conversion) based on the format as specified by the current user’s time zone and locale setting. - For the Web API, the attribute is exposed as DateTimeOffset. - This behavior should be used for attributes that store information such as check in and check out time for hotels.

The following sample code demonstrates how to set a UserLocal behavior for a new date time attribute:

// Create a date time attribute for the Account entity
// with the UserLocal behavior
dtAttribute = new DateTimeAttributeMetadata
{                             
    SchemaName = "new_SampleDateTimeAttribute",
    DisplayName = new Label("Sample Date Time Attribute", _languageCode),
    RequiredLevel = new AttributeRequiredLevelManagedProperty(AttributeRequiredLevel.None),                
    Description = new Label("Created by SDK Sample", _languageCode),                
    DateTimeBehavior = DateTimeBehavior.UserLocal,
    Format = DateTimeFormat.DateAndTime,
    ImeMode = ImeMode.Disabled
};

CreateAttributeRequest createAttributeRequest = new CreateAttributeRequest
{
    EntityName = Account.EntityLogicalName,
    Attribute = dtAttribute
};
_serviceProxy.Execute(createAttributeRequest);
Console.WriteLine("Created attribute '{0}' with UserLocal behavior\nfor the Account entity.\n", 
                            dtAttribute.SchemaName);

In the sample code, you can also set the value of the DateTimeBehavior property by directly specifying the string value: DateTimeBehavior = "UserLocal"

If you do not specify the behavior while creating a date and time attribute, the attribute is created with the UserLocal behavior by default. For the complete sample code, see Sample: Convert date and time values.

Important

• Once you create a date and time attribute with behavior set to DateOnly or TimeZoneIndependent, you cannot change the behavior of the attribute. More information: Change the behavior of a DateTime attribute

  • The date and time attributes with the DateOnly or TimeZoneIndependent behavior will be treated like having the UserLocal behavior when edited in an earlier version of the Dynamics 365 for Outlook client in the offline mode. This is because the client doesn’t understand the new behaviors and won’t treat them differently from UserLocal (existing behavior in versions of Dynamics 365 Customer Engagement (on-premises) earlier than CRM Online 2015 Update 1 or Dynamics 365 (online), version 8.2 (on-premises)). No date and time attributes are converted to the new behaviors on upgrade so the best practice here would be to upgrade all Dynamics 365 for Outlook clients to the latest release before a customizer adopts one of the new behaviors. When online, editing data for fields with the new behaviors will work fine.

  The older Dynamics 365 for Outlook clients will also not understand dates earlier than 1/1/1900 (the earliest value supported for date and time types in versions of DDynamics 365 Customer Engagement (on-premises) earlier than CRM Online 2015 Update 1 and Dynamics 365 (online), version 8.2 (on-premises)). Users won’t be able to open records with dates earlier than 1/1/1900 when offline. However, everything will work fine when they are online. You must upgrade to the latest version of the Dynamics 365 for Outlook clients to work with attributes with dates as early as 1/1/1753 12:00 AM in the offline mode as well.

  • If you use custom code to implement date time behavior in your Dynamics 365 Customer Engagement (on-premises) instance, it might not work as expected in CRM Online 2015 Update 1 and Dynamics 365 (online), version 8.2 (on-premises) owing to the new behavior.

Specify format of the date and time attribute

Use the Format property to specify the date/time display format of the attribute irrespective of how it is stored in the system. You can use the DateTimeFormat enumeration (DateTimeFormat EnumType or DateTimeFormat enumeration) to specify the display format: DateAndTime or DateOnly.

If the DateTimeAttributeMetadata.DateTimeBehavior property is set to DateOnly, you cannot set or change the value of the DateTimeAttributeMetadata.Format property to DateAndTime.

Date and time query operators not supported for DateOnly behavior

Time-related query operators are not supported for the DateOnly behavior. Other than the time-specific query operators listed here, all the other query operators are supported.

• Older Than X Minutes

• Older Than X Hours

• Last X Hours

• Next X Hours

  More information: Date and time query operators in FetchXML

Change the behavior of a date and time attribute

You can update a date and time attribute to change its behavior if you have the System Customizer role in your Dynamics 365 Customer Engagement (on-premises) instance and the DateTimeAttributeMetadata.CanChangeDateTimeBehavior managed property for the date and time attribute is set to True.

Caution

Before changing the behavior of a date and time attribute, you should review all the dependencies of the attribute, such as business rules, workflows, and calculated or rollup attributes, to ensure that there are no issues as a result of changing the behavior. System Customizers can restrict modifying the behavior of existing date and time attributes using the DateTimeAttributeMetadata.CanChangeDateTimeBehavior managed property.

At the minimum, after changing the behavior of a date and time attribute, you should open each business rule, workflow, calculated attribute, and rollup attribute record that is dependent on the changed date and time attribute, review the information, and save the record to ensure that the latest attribute behavior and value is used.

After changing the data and time behavior of a calculated or rollup attribute, open the calculated or rollup field definition editor, and save the field definition to ensure that the attribute is still valid after the behavior change. System customizers can open the field definition editor for calculated or rollup attribute by clicking Edit next to Field Type in the customization area in Dynamics 365 Customer Engagement (on-premises). More information: Define calculated fields and Define rollup fields

• The behavior of the CreatedOn and ModifiedOn attributes for the out-of-box and custom entities is set to UserLocal by default, and the DateTimeAttributeMetadata.CanChangeDateTimeBehavior managed property is set to False, which implies that you cannot change the behavior of these attributes. Although users can change the value of the DateTimeAttributeMetadata.CanChangeDateTimeBehavior managed property of these attributes for custom entities, but they still can’t change the behavior of the attributes.

• For new custom date and time attributes, the DateTimeAttributeMetadata.CanChangeDateTimeBehavior managed property is set to True. This implies that you can change behavior of a custom date and time attribute from UserLocal to either DateOnly or TimeZoneIndependent; no other behavior transitions are allowed.

  For custom date and time attributes that are part of a Dynamics 365 Customer Engagement (on-premises) organization that is upgraded to CRM Online 2016 Update or Dynamics 365 (online), version 8.2 (on-premises), the DateTimeAttributeMetadata.CanChangeDateTimeBehavior managed property is set to True unless the attribute or the parent entity is not customizable.

  Note

  When you update DateTimeAttributeMetadata.DateTimeBehavior property of an attribute from UserLocal to DateOnly, ensure that you also change theDateTimeAttributeMetadata.Format property from DateAndTime to DateOnly. Otherwise, an exception will occur.

• The following out-of-box date and time attributes in CRM Online 2015 Update 1 and Dynamics 365 (online), version 8.2 (on-premises) are by default set to DateOnly and the DateTimeAttributeMetadata.CanChangeDateTimeBehavior managed property is set to False of these attributes, which implies that you cannot change the behavior for these attributes:

  Date and time attribute	Parent entity
  anniversary	Contact
  birthdate	Contact
  duedate	Invoice
  estimatedclosedate	Lead
  actualclosedate	Opportunity
  estimatedclosedate	Opportunity
  finaldecisiondate	Opportunity
  validfromdate	Product
  validtodate	Product
  closedon	Quote
  expireson	Quote

  However, if these out-of-box date and time attributes belong to an organization that is upgraded to Dynamics CRM Online 2016 Update or Dynamics 365 (online), version 8.2 (on-premises), the behavior of these attributes is set to UserLocal and the DateTimeAttributeMetadata.CanChangeDateTimeBehavior managed property to True in the upgraded organization, and you can change the behavior of these attributes to DateOnly only. No other behavior transitions are allowed.

  After updating the behavior of an attribute, you must publish the customizations for the change to take effect. Updating the behavior of a date and time attribute ensures that all the values entered/updated after the attribute behavior was changed, are stored in the system as per the new behavior. This does not impact the values that are already stored in the database, and they continue to be stored as UTC values. However, when you retrieve the existing values using SDK or view it in the UI, the existing values are displayed as per the new behavior of the attribute. For example, if you changed the behavior of a custom attribute on an account entity from UserLocal to DateOnly and retrieve an existing account record using SDK, the date and time will be displayed as <Date> followed by time as 12 AM (00:00:00). Similarly, for the behavior change from UserLocal to TimeZoneIndependent, the actual value in the database will be displayed as is without any time zone conversions.

  The following sample code demonstrates how to update the behavior of a date and time attribute:

  
  
  // Retrieve the attribute to update its behavior and format
  RetrieveAttributeRequest attributeRequest = new RetrieveAttributeRequest
  {
      EntityLogicalName = Account.EntityLogicalName,
      LogicalName = "new_sampledatetimeattribute",
      RetrieveAsIfPublished = false
  };
  // Execute the request
  RetrieveAttributeResponse attributeResponse =
                  (RetrieveAttributeResponse)_serviceProxy.Execute(attributeRequest);
  
  Console.WriteLine("Retrieved the attribute '{0}'.",
                  attributeResponse.AttributeMetadata.SchemaName);
  
  // Modify the values of the retrieved attribute
  DateTimeAttributeMetadata retrievedAttributeMetadata =
                  (DateTimeAttributeMetadata)attributeResponse.AttributeMetadata;
  retrievedAttributeMetadata.DateTimeBehavior = DateTimeBehavior.DateOnly;
  retrievedAttributeMetadata.Format = DateTimeFormat.DateOnly;
  
  // Update the attribute with the modified value
  UpdateAttributeRequest updateRequest = new UpdateAttributeRequest
  {
      Attribute = retrievedAttributeMetadata,
      EntityName = Account.EntityLogicalName,
      MergeLabels = false
  };
  _serviceProxy.Execute(updateRequest);
  Console.WriteLine("Updated the behavior and format of '{0}' to DateOnly.",
      retrievedAttributeMetadata.SchemaName);
  
  // Publish customizations to the account entity
  PublishXmlRequest pxReq = new PublishXmlRequest
  {
      ParameterXml = String.Format("<importexportxml><entities><entity>account</entity></entities></importexportxml>")
  };
  _serviceProxy.Execute(pxReq);
  Console.WriteLine("Published customizations to the Account entity.\n");
  
  

  For the complete sample code, see Sample: Convert date and time values.

Convert behavior of existing date and time values in the database

When you update a date and time attribute to change its behavior from UserLocal to DateOnly or TimeZoneIndependent, it does not automatically convert the existing attribute values in the database. The behavior change affects only those values that will be entered or updated in the attribute after the behavior has been changed. The existing date and time values in the system continue to be in UTC, and displayed by Dynamics 365 Customer Engagement (on-premises) according to the new behavior when retrieved through SDK or in the UI as explained in the previous section. For attributes whose behavior has changed from UserLocal to DateOnly, you can convert the existing UTC values in the database to appropriate DateOnly value to avoid any data anomalies by using the ConvertDateAndTimeBehavior message.

The message enables you to specify a conversion rule (If working with Organization Service see, ConversionRule) to select the time zone to use for conversion of the values from UTC to DateOnly. You can specify one of the following conversion rules:

• SpecificTimeZone: Converts UTC value to a DateOnly value as per the specified Dynamics 365 Customer Engagement (on-premises) time zone code. In this case, you also need to specify a value for the TimeZoneCode parameter.

• CreatedByTimeZone: Converts UTC value to a DateOnly value that the user who created the record would see in the UI.

• OwnerTimeZone: Converts UTC value to a DateOnly value that the user who owns the record would see in the UI.

• LastUpdatedByTimeZone: Converts UTC value to a DateOnly value that the user who last updated the record would see in the UI.

  You can use one of the four members of the DateTimeBehaviorConversionRule class to specify a valid value for the ConversionRule parameter.

Note

• The ConvertDateAndTimeBehavior message is available only if you are using CRM Online 2015 Update 1 or Dynamics 365 (online), version 8.2 (on-premises). It is not available for earlier versions of Dynamics 365 Customer Engagement (on-premises).
  • You must have the System Administrator role in your Dynamics 365 Customer Engagement (on-premises) instance to execute the ConvertDateAndTimeBehaviorRequest message.

When you execute the ConvertDateAndTimeBehavior (If working with Organization Service see, ConvertDateAndTimeBehaviorRequest message), a system job (asynchronous operation) is created to run the conversion request. The ConvertDateAndTimeBehaviorResponse.JobId attribute in the message response displays the ID of the system job that is created as a result of the conversion request. After the system job completes, check the job details (AsyncOperation.Message) to view conversion details or errors, if any.

Note

We recommend that you group conversion of multiple attributes into a single conversion job, and run a single conversion job at a time to ensure that there are no conflicts in the conversion and for optimum system performance.

Some important points to be considered while using the ConvertDateAndTimeBehavior message:

• You should avoid any major changes to the solutions in Customer Engagement during the execution of the message such as importing a solution or deleting an attribute or parent entity. Doing so might lead to unexpected behavior; however no data loss will occur.

• Updates done in the system as a result of executing the message won’t run workflows and plug-ins.

• Updates done in the system as a result of executing the message won’t change the “last modified on” value for the attributes, but will be audited to help the administrators to determine the time of the conversion and the original/changed values for an attribute.

  The following sample code shows how to use the message:

  
  
  ConvertDateAndTimeBehaviorRequest request = new ConvertDateAndTimeBehaviorRequest()
  {
      Attributes = new EntityAttributeCollection() 
              { 
                  new KeyValuePair<string, StringCollection>("account", new StringCollection() 
                  { "new_sampledatetimeattribute" }) 
              },
      ConversionRule = DateTimeBehaviorConversionRule.SpecificTimeZone.Value,
      TimeZoneCode = 190, // Time zone code for India Standard Time (IST) in CRM
      AutoConvert = false // Conversion must be done using ConversionRule
  };
  
  // Execute the request
  ConvertDateAndTimeBehaviorResponse response = (ConvertDateAndTimeBehaviorResponse)_serviceProxy.Execute(request);
  
  

  For the complete sample code, see Sample: Convert date and time values

See also

Sample: Convert date and time values
Behavior and format of the Date and Time field
Customize Entity Attribute Metadata
ConvertDateAndTimeBehaviorRequest
DateTimeAttributeMetadata