Configure the behavior and format of the date and time column by 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. Use the DateTimeAttributeMetadata (DateTimeAttributeMetadata entity type or DateTimeAttributeMetadata Class) 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. 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. For more information, see Behavior and format of the Date and Time column.

Specify the behavior of a date and time column

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 regardless of how the system stores it. Use the DateTimeFormat enumeration (DateTimeFormat enum type or DateTimeFormat Enum) to specify the display format: DateAndTime or DateOnly.

If you set the DateTimeAttributeMetadata.DateTimeBehavior property 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 by using Web API operation, the calculation doesn't occur, which results in unexplained data displays. For example, if you're 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

The UI sets the value to user local, and the 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 value 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

The UI sets the value to user local, and the 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, calculate the offset of the user submitting the data and apply the offset.

Using the preceding example: 4/4/2021 12:00 needs to be submitted via the API as 4/4/2021T12:00:00-08:00. The original time and date 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, don't include the Z, a UTC indicator, because Dataverse doesn'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 means 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, 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 setting means you can change the 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 occurs.

• The following out-of-box date and time columns in Dataverse are set to DateOnly by default and the DateTimeAttributeMetadata.CanChangeDateTimeBehavior managed property is set to False, which means 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 or updated after the column behavior was changed are stored in the system as per the new behavior. This change 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 by 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 change the behavior of a custom column on an account from UserLocal to DateOnly and retrieve an existing account record by 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 is 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, the existing column values in the database don't automatically convert. The behavior change affects only those values that you enter or update in the column after you change the behavior. The existing date and time values in the system stay in UTC. Dataverse displays these values according to the new behavior when you retrieve them through SDK or in the UI, as explained in the previous section. For columns whose behavior changes from UserLocal to DateOnly, you can convert the existing UTC values in the database to the appropriate DateOnly value to avoid any data anomalies by using the ConvertDateAndTimeBehavior message.

This message enables you to specify a conversion rule (if you're 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 sees in the UI.
• OwnerTimeZone: Converts UTC value to a DateOnly value that the user who owns the record sees in the UI.
• LastUpdatedByTimeZone: Converts UTC value to a DateOnly value that the user who last updated the record sees in the UI.

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 you're working with SDK for .NET, see ConvertDateAndTimeBehaviorRequest message), you create a system job (asynchronous operation) to run the conversion request. The ConvertDateAndTimeBehaviorResponse.JobId column in the message response displays the ID of the system job that the conversion request creates. After the system job completes, check the job details (AsyncOperation.Message) to view conversion details or errors, if any.

Consider the following points when using the ConvertDateAndTimeBehavior message:

• Avoid 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. Unexpected behavior might occur. 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. However, the updates are audited to help the administrators determine the time of the conversion and the original and 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, the server handles time zone conversion. If a user enters 2018-10-15 07:30 in a Web Client form, the Client API Xrm.Page.getAttribute(<column name>).getValue() returns 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, the client handles time zone conversion. 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 doesn't 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 change 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?