Converting AX 2012 UTC dates

Question

Trying to extract datetime data from AX 2012 R2 I am hitting a problem. The datetime information is stored in two fields "DATETIME" and "DATETIMETZID" - the former holds the UTC datetime value and the latter the TZID that the former was input with. I need to convert the datetime information into the datetime of the timezone where it was input.

Currently in the GMT+1 Copenhagen time zone, my data input is listed as TZID=37001 - but I dont know where to get a translation of the TZID to a +/- offset value. E.g. if I input a datetime now I would get "2016-01-19 14:32" in the datetime field since that is the UTC datetime and a 37001 in the *TZID field.

Answer 1

Maybe, depending on your scenario, you can use SWITCHOFFSET /TODATETIMEOFFSET in T-SQL:

See this links:

http://blog.sqlauthority.com/2010/07/15/sql-server-datetime-function-switchoffset-example/

http://blog.sqlauthority.com/2010/07/16/sql-server-datetime-function-todatetimeoffset-example/

Answer 2

Maybe, depending on your scenario, you can use this DateTimeUtil Methods:

DateTimeUtil::getTimeZoneOffset .

DateTimeUtil::applyTimeZoneOffset .

DateTimeUtil::removeTimeZoneOffset .

DateTimeUtil::getUserPreferredTimeZone()

Do a job trying to manipulate your date scenario using DateTimeUtil. Anything, try to expose more examples your problem.

https://msdn.microsoft.com/en-us/library/datetimeutil.applytimezoneoffset.aspx

https://msdn.microsoft.com/en-us/library/cc554388(v=ax.50).aspx