The way DateTime-fields are handled in Navision has significantly changed with the newest release NAV 2009. In prior Navision versions the DateTime values are entered in the local time zone of the workstation and stored in UTC on the SQL server (I guess it is the same for the native DB). The values are related through this equation:
GMTDateTime = ClientDateTime + ActiveTimeBias
The ActiveTimeBias (minutes) is stored in the registry (HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\TimeZoneInformation\ActiveTimeBias) and changes typically biannual.
The nav_adg.chm (NAV2009) is telling us:
“This means that the local time representation of a UTC will vary depending on the time zone you are located in and whether you are using daylight saving time. Therefore, any datetimes that were entered during the winter will be converted and displayed differently after you have changed to daylight saving time for the summer. Datetimes will not necessarily be displayed the same as when they were entered. “
This logic allows a straight forward implementation of DateTime conversion between different time zones. This is no longer possible in NAV2009.
The new version changed dramatically: The DateTime fields now show the same value no matter what the current daylight saving time setting is on your client machine. Prior to NAV2009, all DateTimes were displayed to the end-user using the current “ActiveTimeBasis” (which is only correct if you are looking at DateTimes during the time of year with the same ActiveTimeBasis as the current date). For displaying DateTime value to end-users in the NAV2009 client, this is a good clarifying enhancement. Upon further inspection however, it creates some serious problems with respect to the maintenance of DateTimes both for end-users and in C/AL.
I tried to figure out what’s going on internally. It seems that a DateTime variable is always automatically interpreted in the local time zone, for example the following harmless statement (workstation set to Eastern Standard Time, US setup) does not produce the desired result
DateTimeVariable := CREATEDATETIME(030809D,020000T);
The DateTimeVariable will not equal ‘03/08/2009 02:00 AM’ but ‘03/08/2009 01:00 AM’! This makes it extremely cumbersome to work with DateTime data in C/AL.
Another problem is that it is no longer possible for the user to create every valid UTC timestamp, e.g. with my setup I can’t create UTC ‘11/01/2009 06:30 AM’ (= ‘11/01/2009 01:30 AM Eastern Standard Time) with the classical client.
These changes make it extremely difficult to use Navision in an environment where you have to process data from different time zones (like we do in the US).
Maybe you have an answer to one of my questions:
Is there any official documentation available that describes the DateTime handling in NAV2009?
Are there any recommendations how to work with DateTime variables in NAV2009 and how to generally convert between DateTimes of different time zones?
Thanks.