SQL getdate() function and Navision date display

Styvie

Hi,

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 ?

ara3n

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.

Styvie

Thanks... works like a bomb.