Nav Integration, Nav TIME vs SQL Server DATETIME

Dynamics 365 Business Central/NAV Technical Forum
1

Imcurrently working on a project to integrate a mobile solution to Navision. We have decided on a stagin table route directly into the underlying SQL database.

I an currently having an issue with the Nav Time field, which is seen as datetime on the sql server table. This issue i have is using sql insert commands to just insert a time leads the date to be 31-12-1859, which is not recognised from within the Nav system. As I beieve if data is entered into this table then the date is 1754.

Now if this is the case then I will need to hardcode (not an ideal solution) a date into the mobile end so that the data for the time can be accepted by navision and the import process which has been written.

Can anyone advise me as to what the date should be so that Nav recognises the time, or how to calculate the date or is this an individual setting for SQL server?

2

Use CAST and CONVERT to format the datatime field for Navision

EXAMPLE:

CAST(’'1754-01-01 ‘’+CONVERT(CHAR(8), [StartTime], 108) AS DATETIME)

Where StartTime is the SQL datetime field. This will set the date to 01/01/1754 and display time as hh:mm:ss

3

Thanks for the prompt response. The issue I was having is not with how to enter the datetime or even casting it. I think you may have answered the question.

I am more concerned with why the date is 1754-01-01, and where this comes from.

4

That’s the earliest date that can be entered into a SQL datetime field.

5

Again thanks for your quick response but the earliest datetime in sql server is 1/1/1753, which is why I was a bit confused about 1754 being the date.

6

Yes 1753 is the earliest date. 1754 is a mistake on my part. If you look at a Navision time field in SQL you will see that the date part is 01/01/1753

7

The information i have received from a Navision consultant says:

  • If the field is blank , it uses the value 01/01/1753 00:00:00

  • If the field is populated it used the value 01/01/1754 10:00:00, so 01/01/1754 and the time.

cacan anyone help to explain this?

8

That’s how a Nav developer decided to implement it, when they implemented Nav on SQL.