将Access数据表迁移到SQL Server 2005 [英] Migrating an Access data table to SQL Server 2005

问题描述

我使用标准导入/导出工具将表格带入我的SQL数据库。日期都是CHAR类型。现在，我继续收到一个转换错误，说明CHAR到datetime导致超出范围的条件。请帮助。

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.

推荐答案

SQL Server中CHAR（x）类型的字段将以空格填充到固定长度，例如如果您有CHAR（20），并且您的日期类似2009-04-20，该字段将真正包含2009-04-20，这可能会导致转换为datetime的麻烦。

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.

另外，如果您的CHAR字段完全为空，也可能无法转换为有效的datetime。

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

解决方案：

a）您可以将列的数据类型更改为VARCHAR（x），从而摆脱最可能的不必要的填充：

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）您可以确保检查空的CHAR（x）字段值，而不转换那些（或为其选择默认的datetime值）

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）您可以修剪CHAR（x）字段以删除不必要的填充：

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

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

这篇关于将Access数据表迁移到SQL Server 2005的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！