2

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.

Rasmus Remmer Bielidt
  • 157
  • 4
  • 14
  • FYI: The field also contains Daylight Savings Time rules – Reinard Jan 19 '16 at 16:05
  • 1
    Take a look at table [TimeZonesRulesData](https://msdn.microsoft.com/en-us/library/timezonesrulesdata.aspx), I think column `RuleId` in that table corresponds with the `TZID`. Also related [Axapta/DynamicsAx: UTC datetime conversion](http://stackoverflow.com/questions/1205612/axapta-dynamicsax-utc-datetime-conversion) – FH-Inway Jan 19 '16 at 16:18
  • I know this post is old, but did you find any solution, how to use the TZID to convert the date time? – Englund0110 Mar 02 '17 at 12:20
  • @Englund0110 - I did not get a solution to the original problem. However, I created a couple of functions that would convert to and from UTC based on the DATAAREAID. It's far from an ideal solution since it trashes any change of queries going parallel - but it works until I can find a better solution - which is a big plus. – Rasmus Remmer Bielidt Mar 03 '17 at 08:17
  • Thanks for the reply! My problem is not really a tsql problem, but i have to convert the dates to the correct timezone. But i just found the the TIMEZONERULESDATA and TIMEZONELIST in the AX database. So by mixing those two tables, and getting the TIMEZONEKEYNAME, i can use TimeZoneInfo in .NET to convert to the correct date. :-) – Englund0110 Mar 03 '17 at 10:05

2 Answers2

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/

Rodolfo Recalde
  • 31
  • 5
0

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

Rodolfo Recalde
  • 31
  • 5
  • I think OP is looking for an SQL expression to solve the problem and not for a solution using x++. – FH-Inway Jan 20 '16 at 06:16
  • @Rodolfo, Thank you for the reply - I'm sure it Works great in x++, but I am querying the database directly through ODBC and won't have access to any methods or conversion that relies on x++ code. – Rasmus Remmer Bielidt Jan 20 '16 at 07:01