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

Answers

• Options

  Slawek_Guzek Member Posts: 1,690

  2017-09-07

  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
  

  Slawek Guzek
  Dynamics NAV, MS SQL Server, Wherescape RED;
  PRINCE2 Practitioner - License GR657010572SG
  GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03

  0
• Options

  Vesna_1 Member Posts: 23

  2017-09-07

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

  0
• Options

  Slawek_Guzek Member Posts: 1,690

  2017-09-07

  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
  

  Slawek Guzek
  Dynamics NAV, MS SQL Server, Wherescape RED;
  PRINCE2 Practitioner - License GR657010572SG
  GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03

  0