DateTimes – it’s never the last word!

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.

User Local vs. Time Zone Independent

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:

*Model Driven Apps use the user's time zone settings. Canvas Apps use the local machine's time zone.

What's the difference between Date (Date Only) and Date (Time zone Independent)?

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.

Date Time (User Local) Field Example:

Here is a worked example of the standard behaviour in Date Time User Local fields:

Notice how user 2 sees the date as 19^th Jan even though user 1 entered it as 20^th Jan.

Date Only (User Local) Field Example:

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:

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 19^th for User 2.

All other field types

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.

Model Driven Apps

The behaviour in Model Driven Apps in the UI is simple as shown below (in the same order as the table above).

Canvas Apps

If you build a Canvas app that includes these fields it will look like:

Current issues with the CDS Connector for Canvas Apps:

1. There is an issue with the Date only User Local behaviour where it shows the time element.

1. The formatting of the dates will not honour the formatting of the user in their CDS user settings. You will need to manually handle formatting using the CanvasApps field formatting:
   

1. The DateTimeZone.Local will use the user's local machine's time zone rather than their CDS user settings time zone and so currently you'll need to manually compensate for this since it could lead to a different date/time being shown in the Model Driven App compared to the Canvas App if the two time zones are different.
   

These issues will be fixed in a future release of the CDS connector.

WebApi Date Times

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.

Changing Behaviour

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.

Read more

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