Introduction
Dates in Database systems can be used to store information about multiple types of date and time related events. These include:
- Birthdates
- Contract start and end dates
- Task reminders
- Appointment dates
- Deadlines
- Token Access
- Lodgement due
- Exam times
Although these examples seem to require similar formats, at a database level they may need different attributes to provide the functionality required for the specific purpose.
Challenges
One of the challenges that organisations may face occurs is when members of the organisation, or the clients they serve, operate in different time zones.
The main consideration regarding the presentation of a date to a Dynamics 365 or Power Apps user, involves whether the date is absolute or relative, and whether the time of day is important. Examples of this are:
|
Date function |
Example |
Notes/Comments |
|
1. Record when an event has, or will occur regardless of the time zone of the Dynamics user. |
Date of Birth. Contract start or end date. Membership start or end date. Certification/License renewal.
|
These dates could be regarded as ‘absolute’ because they probably don’t vary according to a time zone. The time of day is not needed for these examples. |
|
2. Activity Reminder. Appointment. |
Reminder to ring someone at a specific time on a specific date. Meeting at a specific time on a specific date. |
The date and time will be set by a user but appear differently to people in different time zones. This means that everyone will connect at the same instance even though the times may appear differently in their respective calendars. |
|
3. Access Token or login time window. |
A token is granted to access a web portal for a specific time period. |
The UTC expiry data will be set according to when the token or access is granted. It is important for the end user to know when the expiry occurs in their own time zone. |
|
4. Task or follow up reminder. |
You set a reminder to follow up a previous meeting or conversation. No specific time is needed to be set to do this. |
You will probably sort the tasks by importance rather than any specific time. |
|
5. Advertise opening hours for the local audience. Webinar for a specific region only. |
Restaurant trading hours. Webinar for employees of the local branch. Application or exam needs to be lodged by. |
Just in case, it may be worth advertising that the times are ‘local’. |
Date structure
Dynamics 365 and Power Apps store their data in Dataverse. Throughout the article we will refer to Dataverse rather than Dynamics 365 or Power Apps when referring to date fields.
Dataverse is built on top of a SQL-based relational database, which means that, internally, it uses Microsoft SQL Server technologies for data storage.
In Dataverse, dates are always stored as UTC. UTC stands for Coordinated Universal Time and it is the primary time standard by which the world regulates clocks and time. It is essentially the same as Greenwich Mean Time (GMT) but with some technical differences. UTC is used worldwide and does not change for daylight saving time, making it the standard for timekeeping across different regions and time zones.
So, when a date is displayed, the application that displays the date will retrieve the UTC date and time from Dataverse. The date will then be displayed according to the configuration of the field displaying the date and time.
Configuration options of a Date and Time field in Dataverse
|
Format |
Advanced Option/Time Zone Adjustment |
Behaviour |
Examples of use/Date Function (list above) |
|
Date only |
Date only |
Users in different time zones will see the same date. It is not time zone-aware and does not adjust based on user settings. The date is stored in UTC, but no time conversion is applied when it’s displayed. The system always displays just the date part, and it ensures that all users see the same date, regardless of their time zone. |
Birthday/Date of Birth. Function 1 |
|
Date only |
User local |
Users in different time zones can, and often will, see different dates. Dynamics stores the value in UTC as YYYY-MM-DD 00:00:00. The Date is converted to the user’s local time zone when displaying the value. |
Date related reminders. Date related deadlines. Meetings. Function 2 and 3 |
|
Date only |
Time Zone Independent |
The field stores and displays the date/time exactly as entered, with no conversion to or from UTC. The value is treated as absolute — what you enter is what everyone sees, regardless of their time zone. It’s similar in effect to “Date only” for Date fields — the key difference is more relevant for how data is stored internally. |
This combination will have the same effect as Date only – Date only. Function 1 or 4 |
|
Date and Time |
User local |
Users in different time zones can, and often will, see different dates. |
Date and time related reminders. Appointments/Tasks. Function 2 |
|
Date and Time |
Time Zone Independent |
The field stores and displays the date/time exactly as entered, with no conversion to or from UTC.
The value is treated as absolute — what you enter is what everyone sees, regardless of their time zone. |
Store hours based on a local branch’s time, not the user viewing it. TV or livestream broadcast times for a specific region Regulatory deadlines that are set in a specific time zone (due by 5 PM EST) Function 5 |
Which Format and Option to use
When deciding on the Format and Option for date field, you need to consider the current and future possibilities for the Users of your Dynamics system, AND, the external stakeholders that may login to a Dynamics Power Page Site.
Can I change settings for a Date field later
Generally, you can change the Format and Advanced Option for date field after it has been created, even if contains data. However, we can’t predict what Microsoft may do in the future so assume you can’t.
For example, in older versions of Dynamics 365 Sales related applications, the system field Birthday, is a Date only field with a Time zone adjustment set to User local. User local is not the recommended setting for Birthday because it can be seen as a different date in different time zones. Unfortunately, it can’t be changed in this case. More recent versions of Dynamics 365 sales related applications have the Time zone adjustment of Birthday set to Date only which is more appropriate.