Linked Object - datetime field with timezone

MartinFK
MartinFK Member Posts: 43
edited 2010-10-01 in NAV/Navision Classic Client
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

Comments

  • kine
    kine Member Posts: 12,562
    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.
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • MartinFK
    MartinFK Member Posts: 43
    Hello Kamil,

    thanks for your reply .... where/how can this be done?
  • kine
    kine Member Posts: 12,562
    All depends on how the table is connected, how (which application) is using the data etc.
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • FDickschat
    FDickschat Member Posts: 380
    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?
    Frank Dickschat
    FD Consulting
  • kine
    kine Member Posts: 12,562
    No, for me the behavior is not good, because you never know what you will see...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • dmccrae
    dmccrae Member, Microsoft Employee Posts: 144
    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.
    Dean McCrae - Senior Software Developer, NAV Server & Tools

    This posting is provided "AS IS" with no warranties, and confers no rights.
  • kine
    kine Member Posts: 12,562
    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:
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.

Categories