Way back in 2011 I blogged about the behaviour of DateTimes in Dynamics CRM (as it was then!). I titled the post 'the last word?' but of course, it's never the last word when it comes to a technology that is always moving forward.
This post aims to explain where we are today with Date & Times fields inside the Common Data Service for Applications (CDS) and PowerApps.
In my previous post, I described the challenges of storing absolute dates such as dates of birth. These dates don't change depending on which timezone you are in. Since then, the PowerPlatform now supports 'Time Zone Independent' dates that will always show the date that they are entered as.
If you choose DateTime as the field type you can then select from 3 'behaviours':
This table summarises the differences between these 3 behaviours:
Affected by User Time Zone in PowerApps?
Time Stored in CDS?
CDS WebApi Read/Write uses time zone?
Can be change once set?
✅ The time element is set to 00:00 minus the user's time zone offset.
✅ Can change to Date Only or Time zone Independent
Time zone independent
✅ Always 00:00 irrespective of time zone
Date & Time
✅ Time is set to whatever is entered by the user with no adjustments.
✅ The time element is set to time entered minus the user's time zone offset.
❌ Always UTC
✅ Can change to Time zone Independent only
*Model Driven Apps use the user's time zone settings. Canvas Apps use the local machine's time zone.
Given that Date fields should not show a time, why then do we have both a Date Only and Time Zone Independent behaviour for these types of fields. It's not clear why there is a distinction, but the effect is that web service only returns the Date element for Date (Date Only) fields and for Date (Time Zone independent) fields 00:00 is always returned irrespective of the time zone.
In a model-driven app the fields look like:
The WebApi returns 00:00:00Z for the Time zone independent field but not the Date Only field. The formatted values are however identical!
I can't think of any reason why this might be useful other than if there some client behaviour that couldn't deal with date only fields and always needed a time element.
Here is a worked example of the standard behaviour in Date Time User Local fields:
Time Zone Offset User 1
UTC +10:00 (Brisbane)
Time Zone Offset User 2
UTC -10:00 (Hawaii)
Time Entered by User 1
Stored in CDS as UTC
20-Jan 04:00 (14:00-10:00 = 4:00)
Shown in App to User 2
19-Jan 18:30 (14:00 - 10:00 + (-10:00) = 18:00)
Notice how user 2 sees the date as 19th Jan even though user 1 entered it as 20th Jan.
For Date only User Local fields, the behaviour is the same except the time is set to 00:00 when entering the date. Here is a worked example:
19-Jan 04:00 (00:00-10:00 = 14:00)
19-Jan 04:00 (00:00 - 10:00 + (-10:00) = 04:00)
Notice here that even though the field is set to Date only it is still affected by the local user's time zone and so the Date shows as the 19th for User 2.
For Time zone independent and Date only fields the calculations are simple – the date time returned is the same as entered irrespective of time zone.
Stored in CDS the same as entered
The behaviour in Model Driven Apps in the UI is simple as shown below (in the same order as the table above).
If you build a Canvas app that includes these fields it will look like:
Current issues with the CDS Connector for Canvas Apps:
These issues will be fixed in a future release of the CDS connector.
When you query, create or update date time fields using the WebApi, remember to always set the value in UTC and compensate for any time zone offsets manually since it will not use the user's time zone at all.
As you can see in the table above, if you have User Local Fields you can choose to change to Date only or Time Zone independent fields which is a one-way process. This does not affect the current values in the database (which will be UTC). New fields will correctly be stored, but you may find that existing values will now show incorrectly because they will be the UTC value original stored in the database. To correct this, you will need to write a conversion program using the ConvertDateAndTimeBehaviorRequest message.
You can find a sample written in c# to change the behaviour here- https://docs.microsoft.com/en-us/dynamics365/customer-engagement/developer/org-service/sample-convert-date-time-behavior
Important: There is a cautionary note here in that you must open and re-save any workflows, business rules, calculated field and rollup field after changing the behaviour of the field.
There is good documentation on the Common Data Service DateTime fields at https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/behavior-format-date-time-field.
Information about changing date time behaviour - https://docs.microsoft.com/en-us/dynamics365/customer-engagement/developer/behavior-format-date-time-attribute#convert-behavior-of-existing-date-and-time-values-in-the-database