Behavior and format of the date and time column

In Microsoft Dataverse, you can specify how date and time values are shown to users and how they're adjusted for time zones.

Two options are available for date and time columns.

  • Behavior: Whether to adjust values for time zones.
  • Format: Whether to display the time portion of the value.

Behavior

Dataverse stores all date and time values in UTC time zone. When your app displays values or processing values entered by users, Dataverse and model-driven apps can adjust for the user's time zone with these Behavior options.

  • User local: Adjust values for the user's time zone. This is the default behavior. You can change this once to another behavior.
  • Time zone independent: No time zone conversion.
  • Date only: No time zone conversion. Unlike Time zone independent, the time portion isn't stored.

The user's time zone is set in personal options, not the system time zone in Windows, Android, iOS, or macOS. However, the system time zone might affect client scripts that work with JavaScript Dates.

Format

All date and time columns have a time portion unless its behavior is Date Only. Format determines whether to display the time portion of the value.

  • Date and time: Displays the date and time of the value.
  • Date only: Displays the date portion of the value only.

Note

Users can still change the time portion if the Format is Date Only. For example, with Web API calls or by using a control that has the time portion. This is different from Date only Behavior, where the time portion is not stored at all.

Usage guidelines

Use Time zone independent behavior when time zone information isn't required, such as hotel check-in times. With this selection, users in all time zones see the same date and time value.

Use Date only behavior when information about the time of the day and the time zone isn't required, such as birthdays or anniversaries. With this selection, users in all time zones see the exact same date value.

Time zone independent behavior with Date only format is practically the same as Date only behavior. Use the former if you aren't sure whether you need the time portion in the future.

Important

Avoid Date only format with User local behavior. Users in different time zones might see a different date, which is not intended in most scenarios. When a user sets a date in a model-driven app, the time portion will automatically be set to midnight of their time zone. This might cause the date to appear a day earlier or later for other users.

Examples

Display values

Dataverse stores 2023-10-15T07:30:00Z (or 2023-10-15 for Date only behavior). Users in the time zone UTC-8 see these in the model-driven app or with a Web API request for the formatted value:

Behavior Format Display value
User local Date and time October 14th, 2023, 11:30 pm
User local Date only October 14th, 2023
Time zone Independent Date and time October 15th, 2023, 7:30 am
Time zone Independent Date only October 15th, 2023
Date only - October 15th, 2023

Enter values in an app

Users in the time zone UTC-8 enter October 14th, 2023, 11:30 pm in a model-driven app. The value is saved in Dataverse as:

Behavior Format Value saved in Dataverse
User local Date and time 2023-10-15T07:30:00Z
User local Date only 2023-10-15T07:30:00Z
Time zone Independent Date and time 2023-10-14T23:30:00Z
Time zone Independent Date only 2023-10-14T23:30:00Z
Date only - 2023-10-14

If the user enters just the date October 14th, 2023, the time portion is assumed to be 12:00 AM.

Behavior Format Value saved in Dataverse
User local Date only 2023-10-14T08:00:00Z
Time zone Independent Date only 2023-10-14T00:00:00Z
Date only - 2023-10-14

Enter invalid values in an app

Different clients have different ways to handle invalid input. For example, in Pacific time zone, daylight saving started on March 12th, 2023 at 2:00 AM, moving the time forward one hour to 3:00 AM. The time between 2:00 AM and 3:00 AM on that day doesn't exist. When users try to enter a value in that time range, apps might do one of the following:

  • Change to the previous or next valid time.
  • Revert to the last known value.
  • Show an error message.
  • Don't show times between 2:00 AM and 3:00 AM in the time picker, so that users can't select them in the first place.

Similarly, different clients have different ways to handle repeated time ranges. For example, in Pacific time zone, daylight saving ended on November 5th, 2023 at 2:00 AM, moving the time backward one hour to 1:00 AM. The time between 1:00 AM and 2:00 AM on that day is repeated twice. A time like 1:30 AM could refer to either time zones. If you need to unambiguously show or enter times in that range, it's best to temporarily switch to a time zone that doesn't use daylight saving.

Get raw values with Web API

Dataverse stores 2023-10-15T07:30:00Z (or 2023-10-15 for Date only behavior). Users in all time zones get these with a Web API request for the value:

Behavior Format Raw value
User local Date and time 2023-10-15T07:30:00Z
User local Date only 2023-10-15T07:30:00Z
Time zone Independent Date and time 2023-10-15T07:30:00Z
Time zone Independent Date only 2023-10-15T07:30:00Z
Date only - 2023-10-15

Get values with Client API

Users in the time zone UTC-8 enter October 14th, 2023, 11:30 pm in a model-driven app. Client API functions like formContext.getAttribute(<column name>).getValue() return the value with time zone adjustments applied:

Behavior Format JavaScript dateValue.toUTCString()
User local Date and time 2023-10-15 07:30 (UTC)
User local Date only 2023-10-15 07:30 (UTC)

For Time zone independent behavior, the JavaScript Date value is in the browser's time zone:

Behavior Format JavaScript dateValue.toString()
Time zone Independent Date and time 2023-10-14 23:30 (browser time zone)
Time zone Independent Date only 2023-10-14 23:30 (browser time zone)

JavaScript date values always have a time component. That's why Date only behavior has a time component of 12:00 AM:

Behavior Format JavaScript dateValue.toString()
Date only - 2023-10-15 00:00 (browser time zone)

Note

JavaScript date values are affected by the browser's time zone, which comes from the device operating system settings.

For User local behavior, the Client API result should be interpreted as a UTC value. Use Date.getUTCDate(), Date.getUTCHours(), etc. to work with it. To get what the user sees, apply getTimeZoneOffsetMinutes. Do not use Date.getDate(), Date.getHours(), etc. because these will show the value in the browser's time zone.

For Time zone independent and Date only behavior, the Client API result should be interpreted as a value in the browser's time zone. Use Date.getDate(), Date.getHours(), etc. to work with it. Don't use Date.getUTCDate(), Date.getUTCHours(), and so on because you don't have to adjust for any time zones.

Change user local behavior

Unless the publisher of a managed solution prevents this, you can change the behavior of existing custom date columns from User local to Date only or Time zone independent. This is a one-time change.

Changing the column behavior affects the column values that are added or modified after the column behavior was changed. The existing column values remain in the database in the UTC time zone format. To change the behavior of the existing column values from UTC to Date only, you might need a help of a developer to convert behavior of existing date and time values in the database.

Warning

Before changing the behavior of an existing date and time column, you should review all the dependencies of the column, such as business rules, workflows, calculated columns, or rollup columns, to ensure that there are no issues as a result of changing the behavior. After changing the behavior of a date and time column, open each business rule, workflow, calculated column, and rollup column dependent on the column that you changed, review the information, and save it, to ensure that the latest date and time column's behavior and value are used.

Change behavior during a solution import

When you import a solution that contains a Date column with User local behavior, you can change the behavior to Date only or Time zone independent.

Note

You can only change the behavior of an existing managed Date only or Date and time column if you are the publisher. In order to make a change to these fields, an upgrade must be made to the solution that added the Date only or Date and time column. More information: Upgrade or update a solution

Prevent changing behavior

If you're distributing a custom date column in a managed solution, prevent people using your solution from changing the behavior by setting the CanChangeDateTimeBehavior managed property to False. More information: Set managed properties for columns

Date and time query operators not supported for Date only behavior

The following date and time related query operators are invalid for the Date only behavior. An invalid operator exception error is thrown when one of these operators is used in the query.

  • Older Than X Minutes
  • Older Than X Hours
  • Last X Hours
  • Next X Hours

See also

Troubleshoot date and time issues in model-driven apps
Create and edit columns
Define calculated columns to automate manual calculations
Column managed properties
Managed properties
Blog: Working with time zones in the Dataverse
Configure the behavior and format of the date and time column using code