Migrating an Access data table to SQL Server 2005

Question

I used the standard import / export tool to bring a table into my SQL database. The dates all came over as CHAR types. Now, I keep getting a conversion error stating the CHAR to datetime resulted in an out of range condition. Help please.

Answer 1

Fields of type CHAR(x) in SQL Server will be padded with spaces to their fixed length, e.g. if you have CHAR(20) and you have a date like "2009-04-20" in it, the field will really contain "2009-04-20 " and that might cause trouble when converting to datetime.

Also, if your CHAR field is totally empty, it might not be able to convert to a valid datetime, either.

Solutions:

a) You could change the datatype for your columns to VARCHAR(x) and thus get rid of the most likely unnecessary padding:

ALTER TABLE YourTable
  ALTER COLUMN YourColumnName VARCHAR(x)

b) You could make sure to check for empty CHAR(x) field values and not convert those (or pick a default datetime value for those)

c) You could trim the CHAR(x) fields to remove unnecessary padding:

CONVERT(LTRIM(RTRIM(YourFieldName)), ......)

Cheers!

Marc

Answer 2

As well as the solution from marc_s, check for out of range values.

• MS Access dates range from January 1, 100 to December 31, 9999
• SQL Server datetime is January 1, 1753, through December 31, 9999

This might explain why it upsized as char not datetime... you may have a date < 1753

This only applies to SQL Server 2005 and before, SQL Server 2008 has datetime2

Answer 3

It is recommended to use the SQL Server Migration Assistant (SSMA) rather than the upsizing wizard built into MS Access. Definitely less painful.