在SQL Server中将datetime列转换为datetime2列？

I have a SQL Server 2005 database with a datetime column. There is already data in the table but now the customer needs dates before 1753. So I decided to migrate the database to a SQL Server 2008 to use the datetime2 type.

我有一个带有datetime列的SQL Server 2005数据库。表中已有数据，但现在客户需要1753年之前的日期。所以我决定将数据库迁移到SQL Server 2008以使用datetime2类型。

However I can't just switch the type of the column from datetime to datetime2. Is there a way to do this conversion or do I have to reimport the data?

但是，我不能只将列的类型从datetime切换到datetime2。有没有办法进行这种转换，还是我必须重新导入数据？

Thank you,

谢谢，

Daniel

丹尼尔

1 个解决方案

#1

However I can't just switch the type of the column from datetime to datetime

但是，我不能只将列的类型从datetime切换到datetime

Sure you can, use ALTER TABLE TableNAme ALTER column ColumnNAme datetime2

当然可以，使用ALTER TABLE TableNAme ALTER列ColumnNAme datetime2

example

例

USE tempdb
GO

CREATE TABLE Test(SomeDate DATETIME)
INSERT Test values (GETDATE())

SELECT * FROM Test
GO

ALTER TABLE Test ALTER column SomeDate datetime2
GO

INSERT Test values ('16000101')

SELECT * FROM Test
GO

#1