Linked Object - datetime field with timezone

MartinFK

Hi all,

this is really weird.
Running 4.03 on SQL2005 with NAV Clients on Citrix.

We have a 3rd party solution running on SQL Server A
Navision is running on SQL Server B, with Server A as linked server.

On the Navision server we have a SQL-view that points to server A.
This SQL-view is included in Navisin as linked object and it displays the values correct.

BUT !!!
There is a datetime field that is not viewed correct.

SQL-Server A (the source): 10:02:24
SQL-Server B (the view on the NAV server): 10:02:24
Navision Client: 12:02:24

It is definitively a timezone issue, because if I cahnge the value on the source server, the value can be ssen in the Navision client, but with the 2hrs. difference.

Checked the clocks on the client and the servers - same.

Any idea, where to look further?

Appreciate any help,
Thanks in advance,
Martin

kine

The time in the table must be saved in UTC. If it is not, SQL will not show the data "As is" but NAV will shift them with the timezone. It is just a question of displaying the data. You see same data, but once in UTC and once in local timezone.

MartinFK

Hello Kamil,

thanks for your reply .... where/how can this be done?

kine

All depends on how the table is connected, how (which application) is using the data etc.

FDickschat

Btw, the timezone is not the only criteria used by the NAV client to display a datetime field. Daylight Saving Time will also effect it. :shock:
In Winter you store in a datetime field Dec.31st, 2010, 17:00. When you look at the content in Summer it will display difefrently as Dec.31st, 2010, 16:00 (or 18:00 :?: )

A little off topic: Can anyone name a good use for this behaviour?

kine

No, for me the behavior is not good, because you never know what you will see...

dmccrae

Yes, the Datetime type in NAV is a UTC type that uses the local machine's timezone information to adjust the date/time according to where you see and manipulate the data, so that it will appear as a local time at that location. Therefore this will be different in different machines.

There was a bug in this type that was corrected in NAV 6.0 SP1, which incorrectly used the daylight savings in affect at the current date, to apply to date/time values entered in a previous date which leads to inconsistent values.

Note that allthough NAV is converting values to/from UTC and local times, the value stored in the datetime field in the SQL Server table is independent of conversion.

kine

dmccrae wrote:

There was a bug in this type that was corrected in NAV 6.0 SP1, which incorrectly used the daylight savings in affect at the current date, to apply to date/time values entered in a previous date which leads to inconsistent values.

Good to know... :-) :thumbsup: