Understand date and time in model driven apps
In many professional scenarios, dates and times are processed. Dataverse offers various ways to store and display them, which often leads to confusion. If the data types are not set correctly at the beginning, it may cause difficulties later on. Here you will learn how to best handle date and time fields.
Understanding Date and Time
Dataverse provides several options for creating date and time fields. These differ in what data is actually stored, how it's displayed, and how it can be processed later. I’ve also encountered situations where I wanted to process two dates, only to find that the stored data didn't allow it or the technology—like calculated columns—simply didn’t support it. So what are the options?
In Dataverse and Model-Driven Apps, date fields effectively have two parameters: format and time zone adjustment. When creating a new column, there's also the data type, but this can't be set independently of the format.
There are two format settings: “Date and Time” and “Date Only.” Time zone adjustment can have up to three settings: “User local,” “Time Zone Independent,” and “Date Only.” The latter is only available if the format is also set to “Date Only.”
Format
The format determines how date and time fields are displayed or what can be entered in forms.
- As the name suggests, “Date and Time” displays both the date and the time.
- “Date Only” displays only the date.
However, this only affects display and input. The underlying data is stored as a timestamp with date and time. Since you can't enter a time in Model-Driven Apps when using Date Only, the time component is set to 00:00.
Time Zone Adjustment
Time zone adjustment is what confuses most developers and causes the most problems. It determines how stored data is displayed across different time zones. The options are:
- User local: Assumes users enter the time in their local time zone. The data is converted to and stored as UTC. Upon display, it's converted back to the user's local time.
- Time Zone Independent (TZI): Data is stored exactly as entered. No conversion happens during input or display.
- Date Only: Available only if the format is set to Date Only. Only the date is stored without a time component. Data is stored and displayed exactly as entered.
Many Possibilities
Taken on its own, the system doesn’t seem too complicated. The format determines what the user sees. Time zone adjustment defines whether the data is adjusted for time zones. However, this results in five configuration options: Datetime format with User local or TZI, and Date Only with User local, TZI, or Date Only adjustment. So which should you choose?
Essentially, there are only three relevant cases: Date Only, User local, or Time Zone Independent.
- “Date Only” should be used when only the date matters (e.g., holidays, birthdays). Time is irrelevant here, so no time zone information is needed. Set the format to Date Only and time zone adjustment to TZI. Avoid using User local here as it can lead to incorrect data (see below). You can also set the time zone adjustment to Date Only, but that might limit processing options.
- Use “Time Zone Independent” when displayed times should remain the same regardless of the time zone. For example, when traveling, I want to know the hotel’s check-out time at the destination, not the equivalent time in my home country. In this case, set format to Datetime and adjustment to TZI.
- Use “User local” when it’s important to display the time in the user’s current time zone or for chronological accuracy. In an IT ticket system, for example, you usually want to know exactly when the ticket was created. It would be wrong if a user in New York sees the same time as someone in Mumbai. Set the format to Datetime and the time zone adjustment to User local.
| Format | Input (UTC+2) | Dataverse | Output (UTC-5) |
|---|---|---|---|
| Date only/TZI | 2025-03-21 | 2025-03-21T00:00 | 2025-03-21 |
| Datetime/TZI | 2025-03-21T23:11 | 2025-03-21T23:11 | 2025-03-21T23:11 |
| Datetime/User local | 2025-03-21T23:11 | 2025-03-21T21:11 | 2025-03-21T16:11 |
Input and output in Model-Driven App form.
What About the Other Options?
If you set both format and time zone adjustment to Date Only, only the date is stored with no time information. This can make sense but generally offers no advantage over Date Only with TZI. A downside is that calculated columns become limited—you can’t combine Date Only fields with TZI or User local fields.
You should absolutely avoid combining Date Only format with User local adjustment. Here, only the date is displayed, but time zone adjustment still happens in the background. If I’m in Germany and enter March 19, 2025, it is saved as 2025-03-19 00:00. Due to time zone conversion, it’s stored as 2025-03-18 23:00 UTC. I won’t notice because the display converts it back. But colleagues in the US will see March 18, 2025. This mismatch is usually not desired and can lead to misinformation.
| Format | Input (UTC+2) | Dataverse | Output (UTC-5) |
|---|---|---|---|
| Date only/Date only | 2025-03-21 | 2025-03-21 | 2025-03-21 |
| Date only/User local | 2025-03-21 | 2025-03-20T22:00 | 2025-03-20 |
Avoid combining Date Only with User local.
Power Automate
So far, this only referred to input and display in Model-Driven Apps. But many applications run Power Automate flows in the background. It's important to know that Power Automate does not consider time zone adjustments when saving. Every input is saved exactly as passed, regardless of the user’s time zone. Only in the formatted output might the User local or Date Only format be applied. That’s why it's generally best to use the utcNow() function when setting dates in Power Automate. But this depends on the use case.
| Format | Input (UTC+2) | Dataverse | Output (UTC-5) |
|---|---|---|---|
| Date only/Date only | 2025-03-21T23:11 | 2025-03-21T23:11 | 2025-03-21T23:11 |
| Datetime/TZI | 2025-03-21T23:11 | 2025-03-21T23:11 | 2025-03-21T23:11 |
| Datetime/User local | 2025-03-21T23:11 | 2025-03-21T23:11 | 2025-03-21T23:11 |
Power Automate stores data exactly as entered.
Conclusion
Dataverse offers a variety of ways to format date and time fields—with or without time zone adjustment. This can be confusing, but if you follow these best practices, you should be fine.
- If you only need the date, use Date Only with TZI.
- If you need a fixed time that's the same worldwide, use Datetime with TZI.
- If you need the user's time or actual chronological order, use Datetime with User local.
- Never combine Date Only with User local.
- Be careful in Power Automate—data is stored exactly as entered.
Comments
Leo Redlich
2025-03-31
Ein neuer Kommentar
Write a comment
Your email address will not be published. Required fields are marked with an *.