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:
Yeah.. You could have guessed – it’s Dynamics, and it’s SQL.
On the SQL side, there is a DateTime column:
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:
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!
Thanks Alex!
just a quick question, where is this conversion happening from the SQL DateTime value to the user local, is it on the Server side / client side. If this happening on the server side how is it reading the user location / timezone? Is it reading from the users’ “Set Personal Options” settings?
What happens when the user move from one time zone to another, for example, one week the user is in London and then the next week in Hong Kong and in then in sometime in LA. Does he need to update his “Set Personal Options” to reflect the Local time zone?
Hi Alex,
Thanks for this info. I have tried this but it’s not behaving as you’ve described. Users who are in the EST timezone are still seeing that date and time converted to their time instead of seeing it as it was entered in the system. Any thoughts on if we have to resave those entries?
Michael
Hi Michael, have you set it up as time-zone independent? If yes, all users are supposed to see the same date/time no matter which time zone they are in (it’s field format in the classic designer – in the maker portal setting it up may look a little different, though, in the end, it’s still the same: https://www.itaintboring.com/dynamics-crm/user-local-behavior-with-date-only-format-whats-that-for/)