Invalid Date - Behaviour using Modify / Insert

Peter_Raw

Hi all,
in the last days i stumbled upon a strange behaviour of our Navision2018 System.
And i think(?) this behaviour is knew. I did not recognize that in the past.

Situation:
We use Nav2018. We have to Import data from a text file.
One field is a date field and the value is "1102025". (The data was send by a customer.)
You can see that one number is missing.
It could be the 11.02.?000 or the 11.0?.2025.
If i do an Evaluate on a date field, the date-value is 11.02.0025.

Lets demonstrate what happens with some Pseudo-Code:

Assume this is the table
MyTable
Field 1: MyNo - Integer (PK)
Field 2: MyDate - Date

Test-Case 1 - Update
There is an existing record in the table.

Evaluate(InvalidDate, '1102025');
MyTable.Get(1);
MyTable.MyDate := InvalidDate;
MyTable.Modify(false);

I get an error message directly after the Modify-Statement.
That is what i suppose.

Test-Case 2 - Insert 
We create a new record. (The GetLastRecord function does a Findlast.)

NewNo := GetLastRecord + 1;
Evaluate(InvalidDate, '1102025');
MyTable.Init;
MyTable.MyNo := NewNo;
MyTable.MyDate := InvalidDate;
MyTable.Insert(false);
SendARocketToTheMoon;
Message('We send a rocket to the moon!!!!');
DoSomeOtherStuff;

Now it gets strange…
My expectation is, that there is an error after the Insert-Statement.
But this does not happen. We send a rocket to the moon!!!
(Or think of other things we could do: we call external services, send emails to customers, .... We just do things, that would not be affected by a database-rollback.)
I get the message that we send the rocket to the moon.
And finally we "DoSomeOtherStuff".
At the very end of the function (or calling function of THAT function) we get an error!

I tried to analyze it and set a breakpoint at "SendARocketToTheMoon". The record-object was filled with "2" and "11.02.0025".

I asked our database administrator and he did a select on that table. Well... the record did not exist in the table.

The error message says that the field in the table has either too long or invalid data.

Test-Case 3 - Insert+Commit
I create a new record and commit it.

NewNo := GetLastRecord + 1;
Evaluate(InvalidDate, '1102025');
MyTable.Init;
MyTable.MyNo := NewNo;
MyTable.MyDate := InvalidDate;
MyTable.Insert(false);
Commit;
SendARocketToTheMoon;
Message('We send a rocket to the moon!!!!');
DoSomeOtherStuff;

I get the error message after/with the commit;

Test-Case 4 - If Insert
I insert the record with an IF.

NewNo := GetLastRecord + 1;
Evaluate(InvalidDate, '1102025');
MyTable.Init;
MyTable.MyNo := NewNo;
MyTable.MyDate := InvalidDate;
If MyTable.Insert(false) then ;
SendARocketToTheMoon;
Message('We send a rocket to the moon!!!!');
DoSomeOtherStuff;

I get the error message directly after/with the "If Insert"-statement.

In case 2-3 the database administrator did NOT see any error in the database logs regarding "faulty" data. Just a "Rollback" in the end.

We thought, that maybe a filter on that log was wrong and tried the following:
(=> The GetLastRecord is a FindLast on the Table)

1. Update an existing record with a valid date-value
2. Commit
3. Insert record with an  invalid date value

In the database logs we found: 

1. Select-Statement (to get the record to be updated)
2. Update-Statement
3. Select-Statement (To get the last record => GetLastRecord-Function)
4. Rollback

There was no Insert-statement with the "faulty" date.

As we know, the date-values can be from 01-01-1751 to 31-12-9999. The SQL-Server should(?) not have any problem with the date.

My suggestion:
There is an internal check in Navision which checks the date BEFORE the SQL-Statement is sent to the database.
Can anybody tell if this is true or at least explain to me, what is happening there?!?

I am not Looking for a solution - i can check the date before the update/insert. I just want to understand what is happening "under the hood". ;-)

Best regards,
Peter