Convert Column from Date to Datetime Sql Server

问题:

I have a column named Lastmodified, with a data type of Date, but it should have been DateTime.

Is there any way of converting the column?

When I use the 'Design' feature of SQL Server Management Studio I get the following error:

Saving changes is not permitted. The changes you have made require the following table to be dropped and re-created.

Not really interested in dropping the table, I just want to know if it is possible to convert a column from Date to Datetime or do I have to delete the column and create a new one with the correct data type?

回答1:

It shouldn't need to drop the table and recreate it, unless that column is taking part in one or more constraints.

You can just do it using SQL:

ALTER TABLE Tab ALTER COLUMN LastModified datetime2 not null

(I chose datetime2 over datetime, since the former is recommended for all new development work, and since the column is currently date, I know you're on SQL Server 2008 or later)

回答2:

That's just a safety setting in SQL Server Mgmt Studio - you can turn it off, if you're adventurous :-)

Disable the checkbox there and you can do whatever you like!

回答3:

You can't change the type of a column in place. You need to create a new column, copy of the values over, and then drop the original column.

SQL Management Studio usually accomplishes this by creating a temporary table with the new column name, copying the values over, dropping the original table with the old column, and then renaming the new temporary table to the new name. Often it does this without people even realizing it.

However, this can be a very invasive approach, especially if you already have a lot of rows in the table, so you may want to just write a SQL script add the new column to the table, copy the values over, drop the original column, and then use sp_rename to change the new temporary column name back to the original column name. This is the same idea as what SQL Management Studio is doing, except they are dropping and recreating the whole table, and you are just dropping and recreating the column.

However, if you DO want to let SQL Manangement Studio do it this way, you can turn off that error message. I believe it was originally added because people did not wantdrop and recreate the table by default. To turn this message off, go to Tools->Options-?Designers, and uncheck the "Prevent saving changes that require table re-creation", then you should be able to save your changes in the designer.