SQL DateTime is different to DateTime shown in Navision

Gerhard_GrasserGerhard_Grasser Member Posts: 2
edited 2007-05-11 in SQL General
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

Comments

  • nunomaianunomaia Member Posts: 1,153
    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.
    Nuno Maia

    Freelance Dynamics AX
    Blog : http://axnmaia.wordpress.com/
  • diptish.naskardiptish.naskar Member Posts: 360
    I was facing exactly the same problem and eventually what nunomaia said is correct.
    Diptish Naskar
    For any queries you can also visit my blog site: http://msnavarena.blogspot.com/
  • krikikriki Member, Moderator Posts: 8,922
    [Topic moved from Navision forum to SQL General forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
    NAV TechDays 2020: 19 & 20 November 2020, Antwerp (Belgium)
  • ThomasHagenmeyerThomasHagenmeyer Member Posts: 4
    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_GuzekSlawek_Guzek Member Posts: 1,684
    edited 2018-10-17
    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.
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • ThomasHagenmeyerThomasHagenmeyer Member Posts: 4
    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 ...)
Sign In or Register to comment.