Native to SQL 4.1 Date Errors

spudnacious

I have to move our 30Gb Native DB to SQL to allow for better data access etc. Problem I'm having is that when doing the restore into SQl there are alot of dates in the tables in the Native DB that are being rejected by the SQL restore. :shock: This occurred first on the Comments Table, and is also happeing in the Sales Invoice Header Table. Navision allowed the date 01/08/0903 :oops: to be stored. This obviously fails during import. ](*,)
The question here is, is there an easy way to validate all dates in all tables in one go?
At the moment I'm corrcting table at a time and doing a backup and restore between the two databases. :-k

ara3n

Yes it is possible to write a routine in 4.0 to do this.

Here is how I would do it.
Field is table field
RecRef is Recordreference
MyFieldRef is fieldreference

Field.setrange(TableNo,1,2000000000);
Field.setrange(type,Field.type::Date);
if field.findset(false,false) then repeat
RecRef.open(Field.TableNo);
MYFieldRef := RecRef.field(field."No.");
MyFieldRef.SETRANGE(0d,111753D);
if RecRef.find('-') then repeat
MyFieldRef := RecRef.field(field."No.");
MyFieldRef.value := 0d; //set it to blank;
RecRef.modify;
until RecRef.next = 0

until field.next = 0;




This is quick and dirty way to do it. You can set filter to run it.
Please run it for a smaller range because it will take for every to run it.
change this
Field.setrange(TableNo,1,2000000000);

to something like this.
Field.setrange(TableNo,1,200);


also if you have a customer license you can't run it on ledger tables so you have to exclude them.
I haven't tested it. So Please test if first on a table and make sure it works
:oops:


I had to write a similar thing to change a field from an option type to a code type. Basically what I had to do was create a temporary field in the table copy the fields from option to this temporary fields. Change the data type and move the value back into the field and delete the temporary field. ALL THROUGH CODE.
:P

kriki

In the upgrade-toolkit, there is a fob (SQL Migration\Migrate.fob) that is used to check for dates that are not acceptable in SQL.
It scans the whole DB and then shows you were there are problems. With the tool you can then also change the dates in correct ones.

kine

As kriki wrote: read the Upgrade toolkit documentation, part about SQL migration. The step by step guide is there...

spudnacious

Thanks. Sorry about the delay, i didn't get notified off the new posts. I'll look into the sql migration fob

ara3n

If you are a client that stores historical information. For example you have items that were made in 400 to 1000 years a go. and you want to store that information. How would you store it as date field? I know you can store it as text. Why the limitation on sql?

spudnacious

Valid Date Range for SQL Server Date/Time Data Types
Values for datetime data types earlier than January 1, 1753 are not permitted in SQL Server. SQL Server rejects all values that do not fall within the range from 1753 to 9999.

Another SQL Server data type, smalldatetime, stores dates and times of day with less precision than datetime datatype. Valid date range for this data type is from January 1, 1900 through June 6, 2079.

It is a programming limitation that you would have to use tect fields to work around.

kine

Why 1753? May be becouse:

1753 was the first year Britain and its colonies (at that time) used the Gregorian calendar...