asked on

Conversion Access to MS SQL SDerver

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

raulggonzalez

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 Libby

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 Merlijn

ASKER

I use SQL Server 2008 R2 Express

Stef Merlijn

ASKER

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?

SOLUTION

Russell Libby

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jmoss111

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)

jmoss111

excuse me, i missed sql 2008

Stef Merlijn

ASKER

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

Stef Merlijn

ASKER

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)?

jmoss111

ISDATE() will tell you if the date is valid

Stef Merlijn

ASKER