SQL DateTime is different to DateTime shown in Navision

Gerhard_Grasser

Hello,

i have the following Problem:

I crated a new Table in Navision.
With an extern Programm I write Data's in this Table.
The Primarykey of the Table is the Timestamp.
The Extern Programm is running in an Windows XP Client on witch is Navision 4.01 is installed. The Navisionclient is the SQL Client.
To create the Timestamp the extern Programm uses the SQL-Syntax now.

If i take a look to the SQL-Table using the Enterprisemanager (SQL-Server is 2005) the Time in the Timestamp is the current Time of the Windows XP Client.

If i open the Objectdesigner and do a Tablerun on the Table the
Timestamp ist not the same... The Timedifference is 2 hours.

What's the Problem here?

--> Sorry for my bad English....

Gerhard

nunomaia

Datetime in Navision data is a UTC time when saved in the database.
It is converted to local time by Navision client based in regional settings.

You are in Germany that haves + 2 hours from UTC.

diptish.naskar

I was facing exactly the same problem and eventually what nunomaia said is correct.

kriki

[Topic moved from Navision forum to SQL General forum]

ThomasHagenmeyer

This behaviour ist still the same and one should be aware of this when using ExternalSQL tables. We're doing so in our NAV 2016 to connect to third-party SQL databases and came across the differing datetime issue today ...

Alle the machines in our network are synchronized regarding the time. SQL table datetime fields are filled by database trigger logic using SQL the getdate()-function and therefore showing the correct time. Looking at it in NAV gives you 2 hours added. So, instead of simply showing the table we'll have to either do calculation in NAV or create a view in SQL to feed the ExternalTable in NAV with a datetime that's 'corrected' to compensate NAV behaviour.

Slawek_Guzek

You should use the GETUTCDATE(), not GETDATE() to store the date/time in your SQL table inside the trigger logic.

The reason is exactly this - NAV stores the dates in the UTC.

Your PC, and also GETDATE() all show the time in your actual time zone. If you are in +2 timezone, the UTC time is 21.00, but what you will see is 23.00 - both on your PC and on SQL Server clock, and when you run query SELECT getdate() in SSMS

If you store the time directly as returned by the GETDATE you are actually storing value already adjusted for the current time zone - NOT in the UTC. NAV reads this date and thinks that it is the UTC, and adds another 2 hours.

If you can switch to GETUTCDATE() - that will make stored values compatible with NAV. If the external table and the date/time column is used in another 3rd party app which requires local time then the intermediary view is probably the second best option.

ThomasHagenmeyer

Thanks for the quick comment! It's 3rd party products, so I'll go for the views (sort of property to switch that for the ExternalSQL datetime fields would be nice ...)