Menu
Simon Gosney
  • About
Simon Gosney

Storage and display of dates and times in Dynamics 365 for Finance and Operations (D365FO)

Posted on 4 July, 20194 July, 2019 by Simon Gosney

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 typeSQL Server data typeExample
DateDATETIME2019-07-04 00:00:00
TimeINT50400
UtcDateTimeDATETIME2019-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:

FieldData TypeDescription
UtcDateTimeFieldDATETIMEUTC representation of date time specified by the user
UtcDateTimeFieldTZIDINTInteger 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 formaten-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 formaten-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.

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • Adding PowerApps connector to Excel workbook (OneDrive)
  • Where is the user provisioning tool on D365FO dev VMs?
  • Storage and display of dates and times in Dynamics 365 for Finance and Operations (D365FO)
  • D365FO 10.0.x: where has the Trace menu item gone?
  • Building an IoT temperature sensor with a Raspberry Pi

Categories

  • Data
  • Dynamics
  • Dynamics 365
  • Finance and Operations
  • IoT

Tags

AX2012 Connector d365 D365FO dates development Dynamics Dynamics 365 Excel F&O Finance and Operations Pi PowerApps Raspberry sensor temperature times Timezones Trace vm

Twitter

Unable to load Tweets

Archives

  • October 2019
  • July 2019
©2019 Simon Gosney