Convert Column from Date to Datetime

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!

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)

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.