Conversion Access to MS SQL SDerver

Stef Merlijn
Stef Merlijn used Ask the Experts™
on
Hi,

I've created some code to accomplish a conversion from a MS Access database to a MS SQL Server database.
This works fine except when a date-field in MS Access contains old dates:
Like 26-10-1071

In such a case I get a cast-error.

Below you find the code I use to convert records from one table.

How can I correct my code so these old dates are coverted correctly to?
procedure DataOverzetten;
    var Veldnaam2 : String;
        j : Integer;
    begin
      while (not ADOTableAccessConversie.Eof) do
      begin
        ADOTableSQLConversie.Insert;
        for j := 0 to Pred(ADOTableAccessConversie.FieldCount) do
        begin
          Veld := ADOTableAccessConversie.FieldList.Fields[j];
          Veldnaam := veld.FieldName;
          Veldnaam2 := Veldnaam;
          try
            if ((Veld.DataType = ftWideString) OR (Veld.DataType = ftMemo) OR (Veld.DataType = ftWideMemo)) then
              ADOTableSQLConversie.FieldByName(Veldnaam2).AsString := ADOTableAccessConversie.FieldByName(veldnaam).AsString
            else if ((Veld.DataType = ftInteger) OR (Veld.DataType = ftAutoinc)) then
              try
                ADOTableSQLConversie.FieldByName(Veldnaam2).AsInteger := ADOTableAccessConversie.FieldByName(veldnaam).AsInteger;
              except
                // Komt voor indien het veld in SQL server een Indentity is (AutoInc).
              end
            else if (Veld.DataType = ftBoolean) then
              ADOTableSQLConversie.FieldByName(Veldnaam2).AsBoolean := ADOTableAccessConversie.FieldByName(veldnaam).AsBoolean
            else if (Veld.DataType = ftDateTime) then
              ADOTableSQLConversie.FieldByName(Veldnaam2).AsDateTime := ADOTableAccessConversie.FieldByName(veldnaam).AsDateTime
            else if (Veld.DataType = ftFloat) then
              ADOTableSQLConversie.FieldByName(Veldnaam2).AsFloat := ADOTableAccessConversie.FieldByName(veldnaam).AsFloat
            else if (Veld.DataType = ftBlob) then
              ADOTableSQLConversie.FieldByName(Veldnaam2).asVariant := ADOTableAccessConversie.FieldByName(veldnaam).AsVariant;
          except
          end;
        end;
        ADOTableSQLConversie.Post;
        ADOTableAccessConversie.Next;
      end;
    end;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi there,

You didn't say where your error appears, but maybe is the format of the date dd-mm-yyyy

in that case you can sort it out with this

SELECT CONVERT(DATE, '26-10-1071', 103)

(**http://msdn.microsoft.com/en-us/library/ms187928.aspx)

to return '1071-10-26' which is a valid date.


good luck
Russell LibbySoftware Engineer, Advisory
Top Expert 2005

Commented:
You didn't mention what version of SQL your using. Prior to 2008, you only got datetime and smalldatetime as date data types in SQL. The earliest date value allowed in datetime is 1753-01-01, so obviously your value of 1071-10-26 is not going to fly. For SQL 2005 and below, you will have to do one of (2) things.

a. change the SQL datatype (eg, to char(10))
b. Skip the old dates and write null to SQL

For SQL 2008, you should change the SQL column type to datetime2, which would allow you to store dates before 1753.

Regards,
Russell
Stef MerlijnDeveloper

Author

Commented:
I use SQL Server 2008 R2 Express
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Stef MerlijnDeveloper

Author

Commented:
The database is already distributed to my customers.
Is there a way to change the columntype to DateTime2 without loosing the dates in it and from within Delphi?

Russell LibbySoftware Engineer, Advisory
Top Expert 2005
Commented:
Yes, by excuting an ALTER TABLE statement

>> ALTER TABLE TheTableName ALTER COLUMN TheColumnName datetime2 <<

Commented:
The valid range of dates in SQL Server is from 1/1/1753 (1-Jan-1753) to 12/31/9999 (31-Dec-9999) can be stored in datetime datatype;smalldatetime date ranges allowable are  1/1/1900 (1-Jan-1900) to 6/6/2079 (6-Jun-2079)

Commented:
excuse me, i missed sql 2008
Stef MerlijnDeveloper

Author

Commented:
rllibby,
There is one issue here: when index constraints are set for the columns, you get an accessviolation. Index/constraint depend on ....
Stef MerlijnDeveloper

Author

Commented:
Is there a way to check the date that is imported and upgrade it when it is too far back in time (before 1/1/1753)?

Commented:
ISDATE() will tell you if the date is valid
Stef MerlijnDeveloper

Author

Commented:
Yes, but how to integrate that into the code I use for the conversion?
Software Engineer, Advisory
Top Expert 2005
Commented:
procedure DataOverzetten;
var  Veldnaam2:     String;
     dtValue:       TDateTime;
     dtMinValue:    TDateTime;
     j:             Integer;
begin

  // Set minimum date time value
  dtMinValue:=EncodeDate(1753, 1, 1);

  while (not ADOTableAccessConversie.Eof) do
  begin

      // Original code
      ...
      //

            else if (Veld.DataType = ftDateTime) or (Veld.DataType = ftDate) then
            begin
              // Get value as date
              dtValue:=ADOTableAccessConversie.FieldByName(veldnaam).AsDateTime;
              // Range check
              if (dtValue < dtMinValue) then
                 // Insert minimum value
                 ADOTableSQLConversie.FieldByName(Veldnaam2).AsDateTime := dtMinValue
              else
                 // Insert date value
                 ADOTableSQLConversie.FieldByName(Veldnaam2).AsDateTime := dtValue;
            end
            else if (Veld.DataType = ftFloat) then
     
            // Rest of original code
Stef MerlijnDeveloper

Author

Commented:
Thank you very much. It is perfect.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial