Avoid back and front DateTime differences

(0) Share

Report

Posted on by Community Member

Hi, how can I avoid datetime differences between back and front in Dynamics 2012 R3? I need what you see in front is the same stored in SQL Server.

I'm thinking in a global configuration in dynamics to define my local utc time, not in a programmer's level converting it each time I use datetime funtions.

Any suggestion or advise I appreciate a lot.

Xavier

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (1)

Suggested answer

Mea_ 60,284 on at

Like (0)

Report

Hi xsanchez,

In SQL AX stores datetime in UTC and AX UI automatically applies user time zone to all datetime controls. So, different users with different time zones will see different time in AX UI.

You can change time zone of your user so you will see same time as it's stored in DB technet.microsoft.com/.../gg243274.aspx

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you ievgen for your answer, but I changed set preferred time to my local time zone but the data base and AX stil have different time +/- 5 hrs (utc differences).

I don't need utc datetime conversión in data base or AX, I just want to see my local datetime in AX and the same datetime in SQL Server... like any software you develop by your own...

I understand AX is a world wide software but I just need to turn off this behavior (avoid utc datetime)

thanks a lot.

Was this reply helpful? Yes No
Mea_ 60,284 on at

Like (0)

Report

Hi xsanchez,

You cannot turn off this behavior, however if you will set you prefer timezone to GMT you will get almost the same time.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you ievgen, I'm going to investigate a Little more, because I'm using GMT-6 local timezone but AX still send to SQL Server utc datetime information "changing" what you had in datetime control (I know to keep global standard times)... I'm almost sure it is EDT utcdatetime class behavior, but I looking for a global solution and not changing each call to datetime functions...

Thank you.

Was this reply helpful? Yes No
Martin Dráb 237,990 Most Valuable Professional on at

Like (0)

Report

Again, if you're using GMT-6 timezone, your local time can't be the same as UTC, therefore therefore there always must be a difference. That's exactly the correct behavior; you just have wrong expectations.

What problem do you have with having UTC values in database? You shouldn't really care about it. Maybe you're trying to access AX DB directly instead of using one of the correct ways.

Was this reply helpful? Yes No
Verified answer

Vilmos Kintera 46,149 on at

Like (0)

Report

You may find more information about how AX is storing utcdatetime and by what rules in the documentation.

msdn.microsoft.com/.../cc518263.aspx

One of the sub-topic is talking about TZID fields as well:

msdn.microsoft.com/.../cc622312.aspx

If you want to do reporting/BI stuff, you need to do the timezone conversion yourself, I assume your original problem was related to that.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you Vilmos, your information es really clear and answer my question totally.

Was this reply helpful? Yes No