Tag: Dynamics 365

I get a lot of questions regarding the behavior of Date Time and Date fields within Dynamics 365 (and CDS). Since the behavior in retrieval is different between the interface and the API, I decided to write down the behavior differences and show what field types result in what kind of results.

The following date/time fields are supported within the CDS (Power Apps Platform):

• Date and Time (Time Zone Independent);
• Date and Time (User Local);
• Date Only (Time Zone Independent);
• Date Only (User Local);
• Date Only (Date Only).

Each type is stored differently in the database which also leads to different results when retrieving the records. To test the different behavior, I created an entity with all different date (and time) field types. After that I created a record with a user in that time zone, to really show the effects I used users in GMT -12, GMT -6, GMT -1, GMT, GMT +1, GMT +6, GMT +12. Every record used the same data to enter in the fields:

• Date and Time (Time Zone Independent): 18-12-2019 00:00;
• Date and Time (User Local): 18-12-2019 00:00;
• Date Only (Time Zone Independent): 18-12-2019
• Date Only (User Local): 18-12-2019;
• Date Only (Date Only): 18-12-2019.

After the records were created, we retrieved the records through the user interface again with users in all different time zones. Below are multiple screenshots of the default behavior through the interface.

• 

• 

• 

• 

• 

• 

As we can deduct from the screenshots the interface behaves in the following manner.

Field Type	Behavior	Value
Date and Time	User Local	Time (and date) is different between time zones. Dependent on two factors: in what time zone the record was created and from what time zone the record is accessed.
Date and Time	Time Zone Independent	Independent of whom created it, and who is accessing this value. The value will always show the same date and time.
Date Only	User Local	Date is different between time zones. It behaves the same as “Date and Time, Time Zone Independent” fields with time set to 00:00.
Date Only	Time Zone Independent	Always shows the same date, independent of whom created it and who is accessing the value.
Date Only	Date Only	Always shows the same date, independent of whom created it and who is accessing the value.

This behavior is kind of expected, and all seems fine. But then we need to integrate these fields with other systems through either the Web API or some connectors and then we get some very interesting results.

If we request the same records through the Web API (and thus CDS) we see how the actual data is ‘stored’ and retrieved and that some valuable logic that is handled in the user interface must be handled ourselves.

The retrieval of the records through the Web API is time zone independent. No matter who is requesting the data, it will always return the same values. If we retrieve the same set of records, the following data is returned for all users.

Name	User Local (Date and Time)	Time Zone Independent (Date and Time)	User Local (Date Only)	Time Zone Independent (Date Only)	Date Only (Date Only)
GMT	2019-12-18T00:00:00Z	2019-12-18T00:00:00Z	2019-12-18T00:00:00Z	2019-12-18T00:00:00Z	2019-12-18
GMT +01	2019-12-17T23:00:00Z	2019-12-18T00:00:00Z	2019-12-17T23:00:00Z	2019-12-18T00:00:00Z	2019-12-18
GMT +06	2019-12-17T18:00:00Z	2019-12-18T00:00:00Z	2019-12-17T18:00:00Z	2019-12-18T00:00:00Z	2019-12-18
GMT +12	2019-12-17T12:00:00Z	2019-12-18T00:00:00Z	2019-12-17T12:00:00Z	2019-12-18T00:00:00Z	2019-12-18
GMT -01	2019-12-18T01:00:00Z	2019-12-18T00:00:00Z	2019-12-18T01:00:00Z	2019-12-18T00:00:00Z	2019-12-18
GMT -06	2019-12-18T06:00:00Z	2019-12-18T00:00:00Z	2019-12-18T06:00:00Z	2019-12-18T00:00:00Z	2019-12-18
GMT -12	2019-12-18T12:00:00Z	2019-12-18T00:00:00Z	2019-12-18T12:00:00Z	2019-12-18T00:00:00Z	2019-12-18

As we can deduct from the table above the Web API behaves in the following manner:

Field Type	Behavior	Value
Date and Time	User Local	Time (and date) is different between time zones. Dependent on the time zone the user was in on the moment that the record was created. The data retrieved is the UTC value.
Date and Time	Time Zone Independent	Independent of whom created it, and who is and accessing this value. The value will always show the same date & time.
Date Only	User Local	Date (and time) is different between time zones. It behaves the same as “Date and Time, User Local” fields with time set to 00:00. The data retrieved is the UTC value.
Date Only	Time Zone Independent	Always shows the same date, independent of whom created it and who is accessing the value.
Date Only	Date Only	Always shows the same date (and only returns the date), independent of whom created it and who is accessing the value.

This means that the behavior in displaying data through the Web API (and CDS connectors using the Web API, or any other integration) can have a different outcome if you don’t handle the time zone conversion yourself.

Field Type	Behavior	Shows the same value through API as in web interface
Date and Time	Time Zone Independent	Yes
Date and Time	User Local	No, data retrieval through web API returns UTC value.
Date Only	Date Only	Yes
Date Only	Time Zone Independent	Yes, despite “date only” also returns time stamp in Web API.
Date Only	User Local	No, data retrieval through web API returns UTC value.

This means that only the User Local fields are the biggest challenge in handling through the Web API. But the conversion is quite simple. Since all values are retrieved in UTC the only thing you must do is determine in which time zone the values should be displayed. For this you can either pick one default time zone and enforce this on all the users of the application, or you can retrieve the time zone settings from the user settings through the Web API and apply this to the retrieved date/time field.

I hoped this helped you get the date and time fields sorted. In my next blog I will cover how to create / update those fields through the Web API.