DateTime Fields on Table

Navisionerz, I got a dilemma

Scenario 1:

I have a bespoke table in NAV which includes a datetime field, when a record is updated/created the datetime field is populated with the current time. In this scenario if the record is created/updated the field is updated. When I compare the same record in both NAV and SQL, the SQL table holds the correct time stamp of i.e. 1230hrs but in NAV the field in the same table/record shows a time of one hour ahead 1330hrs.

When comparing records before and after the beginning of Daylight Savings Time 30/03/2014, the records prior to this date match, but after this date, there is inconsistency.

Note that entries created/updated here are done outside of NAV.

Scenario 2:

I create a sales order, on the header table there is bespoke datetime field which logs the creation date and time. I then compare the times logged and it correctly displayed the time as 1230hrs, but in SQL the same record logged a time of 1130hrs

Note that scenrio 2 was done in NAV.

So I am confused. can anyone help?

Thanks

Take a look at the documentation on the CustomSettings.config property WebServicesDefaultTimeZone. Note that fields of type DateTime are sensitive to time zones. Note also that the time zone used by the system is sensitive to whether the session is RTC or a web service connecting through a published NAV object. Also note that simple Date and Time data types are not sensitive to time zones.

George has a point, and also datetime field in nav is different when we read it from sql server,

and for me to read it correctly from sql server i need to calcdate again to make it same with nav, in my country i’ m using +7, and yes like george said, check time zone settings in both sql server database and nav.

Hi…,

DateTime type fields are stored in SQL as UTC.

But when you check the same in Navision, it is converted to local time as per the time zone regional settings in computer.

So, it might be the reason that the local time of your computer and UTC have 1 hour difference.

Also, you can go through the link for the same:

http://msdn.microsoft.com/en-us/library/dd355274.aspx

Hope this will help you.

+1 hour difference.