I just encountered a strange thing - this is the scenario:
NAV 3.70.B on SQL Server 2000, Windows Server 2003, Client and Server on the same machine
A certain process - a stored procedure from a non-NAV db - writes records into a NAV table. When doing this, the field “Inserted Date/Time” (type datetime) is filled; the sp uses the getdate() function for this. Now it comes: if the system time is e.g. 17:00, getdate() retreives 17:00 and inserts this into the NAV table. But when looking at the record in NAV (via Table Run, Form or C/AL Code) the time is 19:00 - plus 2 hours!!! When querying the record via SQL Query Analyzer, the time is still 17:00 (which is correct). (The date is allways OK)
When NAV is processing the record, the field “Processed Date/Time” is filled, using the CURRENTDATETIME function. E.g. at system time 18:00 NAV inserts 18:00, but when querying this via QA the time is 16:00 - minus 2 hours!
So there is allways an offset of two hours between physical storage and display in NAV. To me this looks like a TimeZone problem: In the current case it’s Copenhagen, GMT+1 (DLST), but as mentioned: Client and SQL Server are running on the same machine, thus using the same TimeZone etc…
What is going on there? What do I have to check/configure/change? Please enlighten me!
I have heard about this undocumented feature before – we had a client with the same “issues” as you are describing.
I can’t remember much about it but seem to remember that Navision stores datetimes in GMT (Greenwich Mean Time) but shows the datetime in local time (accordingly to the time zone of the client).
I’m afraid your absolutely right! Actually it’s not an undocumented feature - I found the answer in the C/SIDE reference guide (sometimes the ungrateful truth is closer than expected):
DATETIME
Use this data type to denote the date and time of day. The datetime is stored in the database as Coordinated Universal Time (UTC). UTC is the international time standard (formerly Greenwich Mean Time, or GMT). Zero hours UTC is midnight at 0 degrees longitude. The datetime is always displayed as local time in Navision. Local time is determined by the time zone regional settings used by your computer. You must always enter datetimes as local time. When you enter a datetime as local time, it is converted to UTC using the current settings for the time zone and daylight saving time.
So, that’s where the offset is coming from … but honestly, isn’t that a bloody stupid “feature”?
This is similar to my understanding, though I think its “Techically” UT, not GMT. Which basically is the time stored in your hardware clock, your software clock is adding an hour for Denmark, then another hour for Summer time, so this looks correct to me. Of course UT (Universal Time) is just he Politically correct version of GMT, and now I can’t remember if GMT changes for Summer time, defintely UT does not.).
I think that the implementation of TIME in Navision is wrong, and that DateTime is correct. What should be happening is that TIME should be stored as UT, and then when displaed in a control, TIME fields shoul dlook at regional settings and display local time.
Since Navsion in the base app rarely uses TIE fields its not a huge issue, but if you are doing any mods yourself that use Time, then its better to stick with DateTime fields, since Citrix alows users to work all over the world, and makes a mess.
The object designer also should use Date time, its a damn nuicance when someone changes an object in a different time zone.
The TIME implementation is correct, it cannot use the TimeZones. Why? Because you do not have date part which you can change if you need go over midnight because time zone…
After user 2 from TZ 2 looks to this data he will see (UTC-1h):
Field1 “Created (Date)” = 15/8/2006
Field2 “Created (Time)” = 1:15:00
Field3 “Created (DateTime)” = 14/8/2006 21:15:00
in case when Time will be working with time zones he will see:
Field1 “Created (Date)” = 15/8/2006
Field2 “Created (Time)” = 21:15:00 ← this is wrong if you will use Field1 as date and Field2 as time!
Field3 “Created (DateTime)” = 14/8/2006 21:15:00
Doh, [:$] I didn’t even think of that. Thanks Kamil, and yes you are 100% right. Now it all adds up. Oddly enough this was a specific problem in the early version of Manufacturing before DateTime existed. I shoudl have remembered that. [O]
