Convert datetime column to datetime2 column in SQL Server?

Question

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.

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?

Thank you,

Daniel

Why can't you just change the type? `alter table TableName alter column ColumnName datetime2`? — Mikael Eriksson, Apr 07 '11 at 13:20
@TheQuant - I take it you are aware about the Gregorian calendar issues when dealing with these dates. (If not just thought I'd mention it!) — Martin Smith, Apr 07 '11 at 13:20
@Mikael Eriksson: You are right..that worked! I always tried to change it in the properties and I got the message that I had to drop the table.. thanks — TheQuant, Apr 07 '11 at 13:30
@TheQuant - Covered here. http://stackoverflow.com/q/3310569/513811 — Martin Smith, Apr 07 '11 at 13:46

SQLMenace · Accepted Answer · 2011-04-07T13:24:11.020

33

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

Sure you can, use ALTER TABLE TableNAme ALTER column 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

edited Apr 07 '11 at 13:24

answered Apr 07 '11 at 13:16

SQLMenace

128,762
24
200
224

7

It's not as simple if your column has dependencies, such as indexes and constraints. There is no other way than dropping and re-creating those. – MarioDS Jun 09 '16 at 13:12

Convert datetime column to datetime2 column in SQL Server?

1 Answers1