SQL getdate() function and Navision date display

StyvieStyvie Member Posts: 77

This may not be such a Navision question, and may be more SQL related, but since it applies to my Navision clients, I thought I might seek an answer here. I am sure that someone else has come across this.

I am using a Web Application to insert some records into a temporary table for process later. The web application captures some information about Warehouse tracking docs etc. Some of this information is then used to create docs to be posted in Navision.

I am using a stored procedure called from the web page to fire, and check certain conditions in the database before accepting the records. Once the records are accepted they are inserted in a table within the navision db. Job Scheduler will pick them up and post them once an hour or whenever it is setup to do so.

My problem relates to a Date Time field on the Temporary table. In the Stored PRocedure I use the SQL function getdate() to insert the Date Time field of when the record was inserted.

This works fine, and in QA I can see the date field looking perfect. In Navision however, the date displays as adjusted by my Timezone, which is GMT +2. This is an issue as some entries then move accross the Date Line. I.e Happened on 01/03/2006 at 11:50pm... but Navision says the transactions happened 02/03/2006 as 1:50am .

Both SQL server and navision client pc are in the same timezone ?

I have even tested a navision client on the SQL server with the same results ?

Any idea's on what I am missing ?


  • ara3nara3n Member Posts: 9,253
    Navision stores datetime in GMT. And in runtime it changes it to local time.
    This is becuase if you have two people in different time zone, they would know at what time this has happened based on their local time.

    In SQL there is another function called. getUTCTime
    This functio will return the datetime based on your local time.
    Ahmed Rashed Amini
    Independent Consultant/Developer

    blog: https://dynamicsuser.net/nav/b/ara3n
  • StyvieStyvie Member Posts: 77
    Thanks... works like a bomb.
Sign In or Register to comment.