Convert Column from Date to Datetime

Question

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?

Answer 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)

Answer 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!