将列从日期转换为日期时间 Sql Server
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8430611/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert Column from Date to Datetime Sql Server
提问by gulbaek
I have a column named Lastmodified, with a data type of Date, but it should have been DateTime.
我有一个名为 的列Lastmodified，其数据类型为Date，但它应该是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:
当我使用 SQL Server Management Studio 的“设计”功能时，出现以下错误：
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 Dateto Datetimeor do I have to delete the column and create a new one with the correct data type?
对删除表并不真正感兴趣，我只是想知道是否可以将列从 转换为Date，Datetime还是必须删除该列并创建一个具有正确数据类型的新列？
回答by Damien_The_Unbeliever
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:
您可以使用 SQL 来完成它：
ALTER TABLE Tab ALTER COLUMN LastModified datetime2 not null
(I chose datetime2over 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)
（我选择datetime2了datetime，因为对于所有新的开发工作都推荐使用前者，并且由于该专栏是当前date，我知道您使用的是 SQL Server 2008 或更高版本）
回答by marc_s
That's just a safety setting in SQL Server Mgmt Studio - you can turn it off, if you're adventurous :-)
这只是 SQL Server Mgmt Studio 中的安全设置 - 如果您喜欢冒险，可以将其关闭 :-)
Disable the checkbox there and you can do whatever you like!
禁用那里的复选框，您可以随心所欲！
回答by Mike Mooney
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.
SQL Management Studio 通常通过使用新列名创建临时表、复制值、删除带有旧列的原始表，然后将新临时表重命名为新名称来完成此操作。通常它会在没有人意识到的情况下做到这一点。
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.
但是，这可能是一种非常具有侵入性的方法，特别是如果表中已经有很多行，因此您可能只想编写一个 SQL 脚本，将新列添加到表中，复制值，删除原始列，然后使用 sp_rename 将新的临时列名改回原来的列名。这与 SQL Management Studio 所做的想法相同，只是它们删除并重新创建整个表，而您只是删除和重新创建列。
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.
但是，如果您确实想让 SQL Manangement Studio 这样做，您可以关闭该错误消息。我相信它最初是因为人们不想删除并默认重新创建表而添加的。要关闭此消息，请转到Tools->Options-?Designers，然后取消选中“防止保存需要重新创建表的更改”，然后您应该能够在设计器中保存您的更改。
