kriki: Migrate from Native to SQL Server : dates before 1753/01/01

[Blog bot]

Now that the native database is destined to disappear, it is more than ever time to migrate your database to SQL Server.



Most migrations have some problems with dates.

If you have dates in your system before 1753/01/01 [rambling]Of all date-representations, I prefer the year/month/day because it is the most logical. After all, we write hundred and twenty three as 123 and not  321 (or even worse 213)[/rambling], you have a problem because SQL server does not accept dates before that date in a datetime variable (the variable type used to store C/AL DATE,TIME,DATETIME datatypes).



How to fix it? Of course there is the migrate.fob on the installation DVD to fix it, but if you have a BIG database, it will take a lot of time. And generally, you don’t have much time to do the live-migration. And if you do, it generally means you do it during the weekend and most of the time you would like to be at home with your family during the weekend (at least I do).



And if you have some problem dates, it generally takes time to fix them. In some date-fields you can’t just throw in any date you like (even less the one proposed by the migrate.fob).

Last time I had the problem, I had the accountants turning white and almost having a heart attack when I mentioned that I found some “Posting Date” fields in T17:”G/L Entry” with dates of about 2000 years ago.

Fixing these dates costs time (in this case I was lucky because it was the beta-migration, but still, it had to be fixed in the production database. And after fixing them, it is still possible users put in other bad dates.



So, the best way is to avoid that user can put in bad dates. But how? Actually, it is quite easy.



In codeunit 1, there is a function “MakeDateText(VAR DateText : Text[250]) : Integer”. This function is called whenever the user fills in a date-field. So you can block when the user puts in a bad date.



This is the code-change you need:



MakeDateText(VAR DateText : Text[250]) : Integer

Position := 1;

Length := STRLEN(DateText);

ReadCharacter(’ ‘,DateText,Position,Length);

IF NOT FindText(PartOfText,DateText,Position,Length) THEN



//START

{OLD CODE

  EXIT(0);

}

  BEGIN

    Date1 := DMY2DATE(1,1,1990);

    EVALUATE(Date,DateText);

    IF Date < Date1 THEN

      ERROR(’Date (%1) must come after %2′,Date,Date1);

    EXIT(0);

  END;

//STOP



It does not stop programs to put in bad dates, but at least it stops users from doing so.



BTW: some things to improve in my code: you might put the minimum date as some setup and instead of using a literal string in the ERROR, you should use a text constant.



These are the steps to make your migration (more) painless:

-Put this code in place as soon as possible (even if you are not planning a migration to SQL Server soon, it will help saving the hearts of your accountants).

-Let the users logout  so when they log in again, the code is active.

-Run the migrate.fob to fix the wrong dates as soon as possible BEFORE the migration (preferable before both beta- and live-migration). You might run the migrate.fob each week or 2 before the migration just to be sure.

-When you get to the live-migration, you don’t need to run the migrate.fob anymore and save some time.



Читать дальше