ENGINEERED CODE BLOG

Dates, Times and Portal Capabilities for Microsoft Dynamics 365

Dates, times, time zones, daylight savings time, and anything related to those concepts is an area that has always been a huge pain in the you-know-where for developers. There are so many rules and variables that it’s hard to be confident that your solution is handling all cases perfectly. Thankfully frameworks like .NET remove a lot of the guesswork, but you still have to be careful. In this post, I’ll investigate how Dynamics 365 and Portals handle dates and times.

The Types of Date/Time Fields Available in Dynamics 365

Prior to CRM 2015 Update 1, there were two options: Date Only fields, and Date Time fields. These two types were very similar, with the only real difference was that in the CRM UI, the time component of a Date Only field was hidden. However, it was still there, and was being automatically set to 12am whenever you set the date.

Both types incorporated time zones; data is saved in the SQL database in UTC, so changes made in the CRM UI were assumed to be in the user’s local time zone, and were always displayed in the time zone of the user viewing the record. For Date and Time fields, this was intuitive for the most part, but led to some strange situations with Date Only fields. The classical example is a contact’s birthday: if a user in the Eastern timezone (UTC-5) set a birthdate as December 2, 1980, it would be saved in the database as December 2, 1980 at 5am, which is the UTC equivalent of December 2, 1980 at 12am Eastern time. If they view the record, they would see December 2, 1980 because December 2, 1980 at 5am is December 2, 1980 at 12am (again, the system convert to local time, and shows just the date component). However, if someone on the west coast (UTC-8) were to view that record, they would see December 1, 1980 because they would only see the date component of December 1, 1980 at 9pm (which is what December 2, 1980 at 5am is on the west coast). Not really what users would expect.

Thankfully CRM 2015 Update 1 provided us with a few more options for Date fields. These options are known as behaviors. The available behaviors depend on whether you dealing with a Date Only field, or a Date and Time field.

Date and Time fields have two possible behaviors:

  • User Local: this is the legacy behavior where dates in the UI are based on the user’s time zone, and they are stored in the database as UTC.
  • Time-zone Independent: the date and time the user specifies is stored in the database as is, without any impact by either the saving user’s time zone, or the time zone of the user looking at the value. So if you choose December 2, 1980 at 8am, that’s exactly what will be stored in the database, without any time zone conversion.

Date Only fields have three possible behaviors:

  • User Local: again, this is the legacy behavior. Time zones are taken into account, but only the date component is surfaced in the UI.
  • Date Only: time zones don’t apply to this type, and the time stored in the database is always 12am. This is the behavior that can be used for birthdays that would ensure that you see the same date regardless of what time zone you are in.
  • Time-zone Independent: as far as I can tell, this is equivalent to Date Only. Since the time zones are not considered for this behavior, and the time is always set to 12am, this acts like Date Only. If anyone out there knows of a practical difference, please let me know in the comments below.

Investigation Results

I wanted to investigate how these different options worked with the Portals capabilities for Dynamics 365.

I created a custom entity with fields for the five different types of dates. For these tests, I set dates to April 1, 2018, and times to 8am. My Dynamics 365 user had a time zone of set to Greenland, which for these dates is UTC-2.

The following table shows what data was returned when performing a FetchXML query, as well as using the Organization Service Context. The column for the Organization Service Context also includes the Kind property of the DateTime returned by the SDK.

Type FetchXML SDK (with DateTime Kind)
Date – User Local 2018-04-01T00:00:00-02:00 4/1/2018 2:00:00 AM – UTC
Date – Date Only 2018-04-01T00:00:00 4/1/2018 12:00:00 AM – Unspecified
Date – Time-zone Independent 2018-04-01T00:00:00 4/1/2018 12:00:00 AM – Unspecified
Date and Time – User Local 2018-04-01T08:00:00-02:00 4/1/2018 10:00:00 AM – UTC
Date and Time – Time-zone Independent 2018-04-01T08:00:00 4/1/2018 8:00:00 AM – Unspecified

The results are as expected: the User Local values take into account the timezone of the user, while the other behavior do not. FetchXML has the bonus of providing time zone information, based on the time zone of the user making the request.

Next, I setup a Web Page on the Portal that contained an Entity Form with the same fields, as well as I used the fetchxml tag in Liquid to perform a query for the same data. It’s important to note that my operating system time zone is Saskatchewan, which is UTC-6.

Type Liquid Entity Form
Date – User Local 4/1/2018 2:00:00 AM 3/31/2018
Date – Date Only 4/1/2018 12:00:00 AM 4/1/2018
Date – Time-zone Independent 4/1/2018 12:00:00 AM 4/1/2018
Date and Time – User Local 4/1/2018 10:00:00 AM 4/1/2018 4:00 AM
Date and Time – Time-zone Independent 4/1/2018 8:00:00 AM 4/1/2018 8:00 AM

You’ll notice a few interesting results:

  • The Date – User Local field exhibits the odd behavior I discussed above; even though the date entered into the CRM was April 1, 2018, the Entity Form displays March 31, 2018. This is because the time zone that the Portal cares about does not come from Dynamics 365 – it uses the browser’s time zone, which is typically based on the operating system’s time zone. Since my time zone is UTC-6, a UTC time of 4/1/2018 2:00:00 AM converts to 3/31/2018 8:00:00 PM. Since the User Local behavior simply shows the date component, that fields displays as the day before.
  • The Date Time – User Local field displays 4/1/2018 4:00 AM in the Entity Form. Again, this is the correct time based on the time zone of the browser, since the UTC value for that field is 4/1/2018 10:00:00 AM, and my browser is set to UTC-6. So the good news is that Entity Forms do the work of taking into account the user’s browser time zone, as well as the various behaviors.
  • When it comes to Liquid, everything returned is in UTC time, so it’s up to the developer to decide how they want to handle time zones. There are a couple of JavaScript library included with Portals that can help: https://momentjs.com/ and http://timeago.yarp.com/. This will help you display UTC times in the user’s local time zone (again, based on their browser). You can also use the Liquid date operators to modify these values if there is a consistent offset that you want. For example:
    {{ result['new_datetime'] | date_add_hours: 6 }}
    

So overall, Portals does a good job handling dates and times. You can think of the built-in components like Entity Forms and Entity Lists as similar to the Dynamics 365 UI, where the time zone conversions are handled automatically, with the main different being that the time zone is based on the browser, and not a setting in Dynamics 365. For Liquid, you can think of this as similar to the SDK, where the results are returned in UTC, and the developer is responsible for converting to local time.

Leave a Reply

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

Contact

Engineered Code is a web application development firm and Microsoft Partner specializing in web portals backed by Dynamics 365. Led by a professional engineer, our team of technology experts are based in Regina, Saskatchewan, Canada.