The D365FO platform is designed to be a multi-region solution, implying that it can be implemented across multiple time zones. To support this, the platform has built in functionality to rationalise the storage and display of date time values.
Platform and SQL Server data types
The D365FO database can store date / time values in three ways:
- combined date and time, eg 2019-07-04 14:00:00
- time only, eg 50400 (14:00:00 represented as seconds past midnight)
- date only, eg 2019-07-04 00:00:00
The platform supports those values with the following data types (each example is a representation of all or a portion of 4 July 2019, 2pm UTC (assuming that the user creating the record has their timezone set to “coordinated universal time“):
| D365FO data type | SQL Server data type | Example |
| Date | DATETIME | 2019-07-04 00:00:00 |
| Time | INT | 50400 |
| UtcDateTime | DATETIME | 2019-07-04 14:00:00 |
How do different data types support time zones?
Neither Date nor Time datatypes support time zones, so values entered in fields of those data types are absolute and are not interpreted based on the inputting user or subsequent viewing users’ timezone settings
The fields that only contain date information or only contain time information do not have time zone capabilities. An example of a field that does not have time zone capabilities is the Invoice Date. Invoice Date extends a date type, carries no time information, is not based on UTC, and has no time zone capabilities.
https://docs.microsoft.com/en-us/dynamicsax-2012/developer/time-zone-overview-and-terminology#time-zones-and-dates
The UtcDateTime data type implements time zone (TZ) support by leveraging a second field with “TZID” appended:
| Field | Data Type | Description |
| UtcDateTimeField | DATETIME | UTC representation of date time specified by the user |
| UtcDateTimeFieldTZID | INT | Integer value describing the user TZ offset from UTC |
The *TZId field holds information about the time zone that the stored UTC value was derived from. The *TZId field also holds the Daylight Savings Time (DST) rules established for the time zone, at the time the value was stored. The DST rules cover an entire year, not just the day that the value was stored.
https://docs.microsoft.com/en-us/dynamicsax-2012/developer/time-zone-and-updating-the-hidden-tzid-field
This means that all date time values stored in UtcDateTime fields are represented in the database as UTC. These fields are automatically converted to and from users’ local timezones by data bound form controls, but for DML statements implemented in X++, note that
Any X++ code that you write needs to store DateTime values in UTC. Your code should convert any non-UTC date/time values into UTC before storage.
https://docs.microsoft.com/en-us/dynamicsax-2012/developer/convert-from-time-zone-before-persisting-in-x#datetime-values-must-be-stored-as-utc
Example of timezone handling with UtcDateTime fields
Scenario: we have D365FO a user in London with their user preferences set as follows:
| Time zone | (GMT) Greenwich Mean Time : Dublin, Edinburgh, Lisbon, London |
| Date format | en-gb |
GMT with daylight saving time is currently UTC +1.
This user has access to a table (Table1), which is defined like this (AOT and SQL Server representations):
The user the table (ahem, via Table Browser), specifying the local time 4/7/2019, 3pm, across the three datetime related data types we discussed earlier, resulting in the following record in the table as displayed by the application:
The record created in the SQL Server database looks like this:
We can see that the FIELDUTCDATETIME field has a representation of the datetime value that is one hour less than that displayed in the application, ie GMT-1, which is UTC. The FIELDUTCDATETIMEZID field holds the offset to indicate that the record was created in GMT with daylight saving, ie UTC+1.
We also have a user in Colarado, time zone MST, UTC-7, with their user preferences for time zone and date configured as follows:
| Time zone | (GMT-07:00) Mountain Time (US & Canada) |
| Date format | en-us |
If this user loads table browser to validate the record created by the London user, they see the following:
As we discussed earlier, the first two fields (Date and Time data types) do not respect timezones, and so the represented value is the same irrespective of time zone, although the formatting has changed match the user’s preference. However, the UtcDateTime value has change to display the time that was entered as 3pm in London as the equivalent local time in Colarado, ie 8am (3pm London = 2pm UTC = 8am MST).
If the user in Colarado wishes to create a second record in Table1 to indicate 9pm local time on 7/4/2019 (slipping into mdy date format), this record is displayed in Table Browser like this:
The SQL Server representation, however, looks like this:
The UTC-6 time zone means that the UTC datetime equivalent is the next day. Note the different FIELDUTCDATETIMETZID value indicating that the record was created in MST.
When the user in London views the record in Table Browser, they see this:
The FieldUtcDateTime value is displays a GMT+1 representation of the time that was specified 7 hours earlier in MST.
Timezone Configuration
We’ve examined the impact of timezone configuration, but where is it set for each user?
User Options | Preferences | Language and Time Zone parameters
If you need to bulk update attributes of users, they can be exported to Excel, amended, then pushed back into the application.
Summary
We have examined three different platform data types that can be used to store either date, time or combined date and time values, and we have examined how two of the three data types are not time zone sensitive, but the third (UtcDateTime) in conjunction with core platform functionality has the following characteristics:
- UTC equivalent of datetime value persisted in database
- Timezone (TZ) value (integer data type) also persisted to indicate user TZ alongside UTC-aligned datetime value
- Datetime value automatically aligned with user TZ configuration at runtime to display in local timezone (data bound form controls)
- Data logic implemented in X++ must manually account for TZ before comparing or persisting dates
See Microsoft documentation here for further background.