How to send SQL data type datetime to Navision fild (also datetime)?

Vesna_1

When I do this Navision send me error that date is not correct.
Also when I send datetime from NAV , SQL only receives date part and zeros instead of time.

Slawek_Guzek

There are 3 types of date/time datatypes in NAV: DATE, TIME, and DATETIME. All three are stored as the datetime type in the SQL table. You need to know which one are you dealing with to interpret results correctly.

The DATETIME is stored as is.

The TIME is stored as '1754-01-01' data plus a time part on it

The DATE is stored as an actual date + 00:00:00 time part, or, in case of closing dates, as an actual date + 23:59:59 time


Having said that - you have probably already read this elsewhere but I will repeat - writting data directly to NAV tables at the SQL level is not supported for various technical (and also legal) reasons. You may get yourself into troubles by rendering your NAV data inconsistent. And quite likely if your NAV partner discovers that you have some processes updating your NAV directly they will refuse supporting you in case of problems.

Slawek

Vesna_1

What is alternative way to get date and time to navision?

Slawek_Guzek

Web services are the way to go. Requires a bit of coding from NAV side to expose a web service, and calling the web serivice is more complicated than directly updating a table in SQL, but it warrants executing NAV validation code, if any is defined. And it also updates data in the service tier cache - so NAV clients will see the updated data immediately.

Web services require the NAV server (NST server) to be up and running. If you are on NAV2013+ then you already have it otherwise NAV woudn't work. If you are using classic client in version predating NAV 2013 you may need to set it up as it was not mandatory back then.

If you are on version pre NAV2009 R2 then, hate to say that, updating a table directly on SQL Server level seems to be the most sensible option, as to get any sort of communication / over-the-network request handling was a pain in the bottom, required a lot of coding in NAV. In older version primary build-in method of data exchange was through files.

Slawek