Dates in Dynamics: TimeZone Independent vs User Local

By | December 18, 2017

Did you ever have to look into the DateTime fields behaviour in Dynamics? DateTime fields can be “User Local”, “Date Only”, and “Time Zone independent”:

https://technet.microsoft.com/en-us/library/dn946904.aspx

If what you see at that link does not look 100% clear (and, to me, it did not..  until a few days ago), let me try to explain it from a little different perspective.

See, there are two sides to it:

image

Yeah.. You could have guessed – it’s Dynamics, and it’s SQL.

On the SQL side, there is a DateTime column:

image

What happens to that column in Dynamics depends on the behavior.

If it’s “User Local”, that timestamp will show up in the Dynamics interface in the user timezone. A Dynamics user in EST timezone, for example, would see it as 7:28 AM.

If it’s “Time Zone Independent”, it would show up in the Dynamics interface as is. In other words, it would be 12:28 PM for a user in ANY timezone.

If it’s “Date Only”, it’ll actually show up as Date Only: 12/10/2017 for a user in ANY timezone.  That’s why, if you create a DateTime field with Date Only behavior, it will be stored in SQL with 00:00 for time.

But that’s where you may see some interesting side effects if you ever have to change the behavior of an existing field.

You can’t do it for “created on”, but imagine your own Date and Time, User Local field. If you go to Dynamics to change the behavior of that field to “Time Zone Independent”, for example:

image

This is going to change how Dynamics will be treating the field, but not what will be stored in SQL for the existing data. If there were timestamps for 12/10/2017 12:58PM, they will not be updated. If there were timestamps for 12/10/2017 4:00 AM, they will not be updated.

However, for a user in EST, that 4:00 AM used to show up as 12/9/2017 23:00 PM before the change (UTC-5). After the change, it will be showing up as 12/10/2017 4:00 AM (no conversion from what’s stored in SQL).

Hope this helps.

Happy 365-ing!

Leave a Reply

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