将列从日期转换为日期时间
我有一个名为 Lastmodified 的列，数据类型为 Date，但它应该是 DateTime。
有没有办法转换列？
当我使用 SQL Server Management Studio 的“设计”功能时，出现以下错误：
不允许保存更改。您所做的更改需要以下内容 要删除并重新创建的表。
对删除表格不太感兴趣，我只是想知道是否可以将列从 Date 转换为 Datetime 或者我是否必须删除该列并创建一个具有正确数据类型的新数据？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
它不需要删除表并重新创建它，除非该列参与一个或多个约束。
您可以使用 SQL 来完成此操作：（
我选择了
datetime2而不是datetime，因为前者是 推荐用于所有新的开发工作，并且由于该列当前为日期，我知道您使用的是 SQL Server 2008 或更高版本）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:
(I chose
datetime2overdatetime, since the former is recommended for all new development work, and since the column is currentlydate, I know you're on SQL Server 2008 or later)这只是 SQL Server Mgmt Studio 中的一个安全设置 - 如果您喜欢冒险，可以将其关闭:-)
禁用那里的复选框，您可以做任何您喜欢的事情！
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!
您无法就地更改列的类型。您需要创建一个新列，复制值，然后删除原始列。
SQL Management Studio 通常通过创建具有新列名称的临时表、复制值、删除具有旧列的原始表，然后将新临时表重命名为新名称来实现此目的。通常它会在人们没有意识到的情况下这样做。
然而，这可能是一种非常侵入性的方法，特别是如果表中已经有很多行，因此您可能只想编写一个 SQL 脚本，将新列添加到表中，复制值，删除原始列，然后使用 sp_rename 将新的临时列名更改回原始列名。这与 SQL Management Studio 所做的想法相同，只不过它们删除并重新创建整个表，而您只是删除并重新创建列。
但是，如果您确实想让 SQL Manangement Studio 这样做，您可以关闭该错误消息。我相信最初添加它是因为人们不想默认删除并重新创建表。要关闭此消息，请转到工具->选项-？设计器，然后取消选中“防止保存需要重新创建表的更改”，然后您应该能够保存您在设计器中的更改。
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.