Behavior and format of the date and time attribute

Note

Effective November 2020:

  • Common Data Service has been renamed to Microsoft Dataverse. Learn more
  • Some terminology in Microsoft Dataverse has been updated. For example, entity is now table and field is now column. Learn more

This article will be updated soon to reflect the latest terminology.

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 Microsoft Dataverse. 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 Dataverse 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 Dataverse support values as early as 1/1/1753 12:00 AM.

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 no time value.
- Retrieving the formatted value displays the date value.
- 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. No date and time attributes are converted to the new behaviors on upgrade so the best practice here would be to upgrade all Dataverse 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.

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 Dataverse 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 Dataverse. 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 Dataverse organization, 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 Dataverse 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

    The behavior of these attributes is set to UserLocal and the DateTimeAttributeMetadata.CanChangeDateTimeBehavior managed property to True, 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 Dataverse 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 Dataverse 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

You must have the System Administrator role in your Dataverse 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 Dataverse 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

Best practices for using time zone

For my Date/Time field I was expecting (UTC/Local) and I am seeing the opposite value

This is caused by a lack of parity between the entity field setting and the app form setting. When an entity field is configured for Time Zone Independent or User Local, it determines if the time zone offset is honored or not when the data is being retrieved from the store. However, the app form also has a setting of UTC or Local.

This tells the form how to interpret the data it receives from the Dataverse. If the data retrieved from the store is time zone independent, but the form is set to local, the UTC data will be displayed as user local time based on the user’s time zone in their profile. The reverse is also true, a user local value from the store will be displayed as UTC if the form is set to UTC. Fortunately, the form’s date time zone values can be modified without disrupting the existing records.

I picked Date Only in my entity field, but my form is showing a time picker along with the date

This would happen if you chose a behavior of time zone independent or user local for your date only field. The Dataverse will store a time of 00:00:00 by default, but if you add the field to a form it will assume you need to set the time as well. If you leave the time pickers in the form, users can enter a time and it will be saved as something other than 00:00:00. Here is how can you fix this.

  • Edit the form and remove the time picker and associated formulas. This will save the time as 00:00:00 and will still allow for time zone based date calculations.
  • If your field is currently set to user local, and you don’t need the date to be time zone calculated, you can change it to date only. This is a permanent change and cannot be undone. This change cannot be made to time zone independent behavior fields. Always be careful changing behaviors as other apps, plugins, or workflows may be relying on the data.

I have a date only field, but it is showing the wrong date for some users

If this happens, check the behavior that is set up for the date only field. If the field is set to time zone independent or user local, the included timestamp will cause the date to appear differently for different users. The form display settings of UTC or Local will determine if the date displayed is calculated using the user’s time zone settings or if it displays as the UTC value. Changing the form values to UTC instead of user local will prevent time zone offset calculations and will display the UTC date for the saved record. Alternately, if you need this to be a static date that does not change and the field is currently user local, you can change the field behavior to Date Only. Be cautious though as this is cannot be undone.

My (script/plug-in) is supposed to intercept the date submitted using the Universal Client before the user local conversion occurs, but instead it is being treated as User Local data

The web client and universal client have slightly different behaviors when it comes to when data is translated between UTC and User Local. In the web client, dates are entered into the client, passed to the API as provided, and converted later into user local time. This allowed scripts/plug-ins to retrieve the data and take action before data was passed to the platform services and translated into user local time. In the universal client, the translation of a date into user local values happens before the data is passed to the API, because of this, the data provided will not be a UTC date but rather a user local date based on the user who retrieved or posted it. To resolve this, a user can either:

  • Change the form to time zone independent which will retain the UTC value. This only works if the user does not need the form to display in user local time.
  • Modify their script to detect the time zone offset used, recalculate back to UTC within the script, and then take action.

See also

Sample: Convert date and time values
Behavior and format of the Date and Time field
Customize Entity Attribute Metadata
ConvertDateAndTimeBehaviorRequest
DateTimeAttributeMetadata Blog: Working with time zones in the Dataverse