Configure the behavior and format of the date and time column using code

If you have users and offices around the world, it's important to properly represent date and time values in multiple time zones. The DateTimeAttributeMetadata (DateTimeAttributeMetadata entity type or DateTimeAttributeMetadata Class ) is used to define and manage columns of type DateTime in Microsoft Dataverse. Use the DateTimeBehavior property (For SDK for .NET see, DateTimeBehavior Property) 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 columns.

You can also use the customization area in Dataverse to define the behavior and format of the date and time columns. More information: Behavior and format of the Date and Time column.

Specify the behavior of a date and time column

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

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

/// <summary>
/// Create a new DateTime column for the Account table with UserLocal behavior
/// </summary>
/// <param name="service">Authenticated IOrganizationService instance</param>
static void CreateUserLocalDateTimeColumn(IOrganizationService service) {

   int _languageCode = 1033; //English

   DateTimeAttributeMetadata dtAttribute = new()
   {
       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 = Microsoft.Xrm.Sdk.Metadata.DateTimeFormat.DateAndTime,
       ImeMode = ImeMode.Disabled
   };

   CreateAttributeRequest request = new()
   {
       EntityName = Account.EntityLogicalName,
       Attribute = dtAttribute
   };

   service.Execute(request);
}

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

If you don't specify the behavior while creating a date and time column, the column is created with the UserLocal behavior by default.

Specify format of the date and time column

Use the Format property to specify the date/time display format of the column irrespective of how it's stored in the system. You can use the DateTimeFormat enumeration (DateTimeFormat enum type or DateTimeFormat Enum) to specify the display format: DateAndTime or DateOnly.

If the DateTimeAttributeMetadata.DateTimeBehavior property is set to DateOnly, you can't 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 aren't 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: Datetime data operators

Using OData APIs to submit user local date and time values

In Microsoft Power Platform, when a user submits a date and time in the user-specific time zone through the UI, an automatic calculation sets the data to the correct date and time. It performs an analysis to change any submitted date to the corresponding UTC value based on the column and UI settings. When you submit a date and time value using Web API operation, the calculation doesn't occur, resulting in unexplained data displays. For example, if you are in the Pacific time zone and you submit 4/4/2021 12:00, the following happens:

• Original: 4/4/2021 12:00 submitter is in the Pacific time zone.
• Submitted through UI and retrieved as user local: 4/4/2021 12:00
• Submitted through API and retrieved as user local: 4/4/2021 04:00

Submitting through the UI

UI is set to user local, and column is set to user local.

• Original Value: 4/4/2021 12:00 in the Pacific time zone.
• Value calculated to UTC and stored in Dataverse: 4/4/2021 12:00 + 8:00 = 4/4/2021T20:00:00Z . This is because PST is -8:00 from UTC, so +8 is added to the stored value.
• Value when displayed in UI by a user in the Pacific time zone: 4/4/2021 12:00. The UI applies the -8:00 UTC offset calculation to 4/4/2021T20:00:00Z for the correct value.

Submitting through the API

UI is set to user local, and column is set to user local.

• Original Value: 4/4/2021T12:00:00 or 4/4/2021T12:00:00Z – no offset or UTC indicator provided. The submitter is in the Pacific time zone.
• Value calculated to UTC and stored in Dataverse: No UI calculation is done on submission from OData APIs, so the value is stored as 4/4/2021T12:00:00Z.
• Value when displayed in UI by a user in the Pacific time zone: 4/4/2021 4:00. The UI applies the -8:00 UTC offset calculation on the value in Dataverse.

To prevent this issue when using API calls to input data to user local columns, you need to calculate the offset of the user submitting the data and apply the offset.

Using the above example: 4/4/2021 12:00 would need to be submitted via the API as 4/4/2021T12:00:00-08:00. The original time and date and include the offset calculation of the current user's time zone. Alternately, the submitter can perform the calculation before submission and submit 4/4/2021T20:00:00Z.

If you choose to include the offset calculation, you can't have the Z, a UTC indicator, because Dataverse won't accept it.

Change the behavior of a date and time column

You can update a date and time column 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 column is set to True.

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

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

  For custom date and time columns that are part of a Dataverse organization, the DateTimeAttributeMetadata.CanChangeDateTimeBehavior managed property is set to True unless the column or the parent table isn't customizable.

  Note

  When you update DateTimeAttributeMetadata.DateTimeBehavior property of a column 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 columns in Dataverse are by default set to DateOnly and the DateTimeAttributeMetadata.CanChangeDateTimeBehavior managed property is set to False of these columns, which implies that you can't change the behavior for these columns:

  Date and time column	Parent table
  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 columns is set to UserLocal and the DateTimeAttributeMetadata.CanChangeDateTimeBehavior managed property to True, and you can change the behavior of these columns to DateOnly only. No other behavior transitions are allowed.

After updating the behavior of a column, you must publish the customizations for the change to take effect. Updating the behavior of a date and time column ensures that all the values entered/updated after the column behavior was changed, are stored in the system as per the new behavior. This doesn't 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 column. For example, if you changed the behavior of a custom column on an account from UserLocal to DateOnly and retrieve an existing account record using SDK, the date and time are 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 column:

/// <summary>
/// Update the behavior of a DateTime column
/// </summary>
/// <param name="service">Authenticated IOrganizationService instance</param>
static void UpdateBehaviorOfDateTimeColumn(IOrganizationService service) {

    // Retrieve the attribute to update its behavior and format
    RetrieveAttributeRequest retrieveColumnRequest = new()
    {
        EntityLogicalName = Account.EntityLogicalName,
        LogicalName = "new_sampledatetimeattribute",
        RetrieveAsIfPublished = false
    };
    // Execute the request
    RetrieveAttributeResponse attributeResponse =
                    (RetrieveAttributeResponse)service.Execute(retrieveColumnRequest);

    // Modify the values of the retrieved attribute
    DateTimeAttributeMetadata retrievedAttributeMetadata =
                    (DateTimeAttributeMetadata)attributeResponse.AttributeMetadata;

    retrievedAttributeMetadata.DateTimeBehavior = DateTimeBehavior.DateOnly;
    retrievedAttributeMetadata.Format = Microsoft.Xrm.Sdk.Metadata.DateTimeFormat.DateOnly;

    // Update the attribute with the modified value
    UpdateAttributeRequest updateRequest = new()
    {
        Attribute = retrievedAttributeMetadata,
        EntityName = Account.EntityLogicalName,
        MergeLabels = false
    };
    service.Execute(updateRequest);


    // Publish customizations to the account 
    PublishXmlRequest pxReq = new()
    {
        ParameterXml = "<importexportxml><entities><entity>account</entity></entities></importexportxml>"
    };
    service.Execute(pxReq);
}
 

Convert behavior of existing date and time values in the database

When you update a date and time column to change its behavior from UserLocal to DateOnly or TimeZoneIndependent, it doesn't automatically convert the existing column values in the database. The behavior change affects only those values that will be entered or updated in the column 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 columns 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 SDK for .NET see, ConvertDateAndTimeBehaviorRequest.ConversionRule property) 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 ConvertDateAndTimeBehaviorRequest.TimeZoneCode property.
• 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 property.

When you execute the ConvertDateAndTimeBehavior (If working with SDK for .NET see, ConvertDateAndTimeBehaviorRequest message), a system job (asynchronous operation) is created to run the conversion request. The ConvertDateAndTimeBehaviorResponse.JobId column 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.

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 a column or parent table. Doing so might lead to unexpected behavior; however no data loss occurs.
• Updates done in the system as a result of executing the message don't run workflows and plug-ins.
• Updates done in the system as a result of executing the message don't change the "last modified on" value for the columns, but will be audited to help the administrators to determine the time of the conversion and the original/changed values for a column.

The following sample code shows how to use the message:

/// <summary>
/// Demonstrates use of the ConvertDateAndTimeBehavior message
/// </summary>
/// <param name="service">Authenticated IOrganizationService instance</param>
static void ConvertDateAndTimeBehavior(IOrganizationService service)
{

    ConvertDateAndTimeBehaviorRequest request = new()
    {
        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 Dataverse
        AutoConvert = false // Conversion must be done using ConversionRule
    };

    // Execute the request
    var response = (ConvertDateAndTimeBehaviorResponse)service.Execute(request);

    Console.WriteLine($"Asynchronous Job ID: {response.JobId}");
}

Web Client handles time zone conversion differently from Unified Interface

Web Client was deprecated in 2019. If you're migrating client scripts to its successor, Unified Interface, note the difference in how the two clients handle time zone conversion for User Local columns.

In Web Client, time zone conversion wasn't done on the client side. If a user entered 2018-10-15 07:30 in a Web Client form, the Client API Xrm.Page.getAttribute(<column name>).getValue() returned 2018-10-15 07:30. This value is sent to the server and time zone adjustments happen there when saving the value.

In Unified Client, time zone conversion is done on the client side. If a user in UTC+8 time zone enters 2018-10-15 07:30 in a Unified Client form, the Client API formContext.getAttribute(<column name>).getValue() returns the adjusted value 2018-10-14T23:30:00Z. The server accepts the value as is and not perform further time zone adjustments.

To account for this difference, you can:

• Get the user's time zone offset with Component Framework UserSettings.getTimeZoneOffsetMinutes method or Xrm.Utility.getGlobalContext().userSettings.getTimeZoneOffsetMinutes method and modify your scripts to account for it.
• Change the column behavior from UserLocal to TimeZoneIndependent so that entered times aren't adjusted. This is only possible if time zones aren't applicable for the column.

See also

Behavior and format of the Date and Time column
Troubleshoot date and time issues in model-driven apps
Columns overview
ConvertDateAndTimeBehaviorRequest class
DateTimeAttributeMetadata class
Blog: Does how you submit date and time data matter?