How to change the data type of a column without dropping the column with query?

Question

I have a column which has a datatype : datetime. But now i want to convert it to datatype varchar. Can i alter the datatype without droppping the column? If yes, then please explain how?

Answer 1

MSDN says

ALTER TABLE mytable ALTER COLUMN mycolumn newtype 

Beware of the limitations of the ALTER COLUMN clause listed in the article

Answer 2

If ALTER COLUMN doesn't work.

It is not unusual for alter column to fail because it cannot make the transformation you desire. In this case, the solution is to create a dummy table TableName_tmp, copy the data over with your specialized transformation in the bulk Insert command, drop the original table, and rename the tmp table to the original table's name. You'll have to drop and recreate the Foreign key constraints and, for performance, you'll probably want to create keys after filling the tmp table.

Sound like a lot of work? Actually, it isn't.

If you are using SQL Server, you can make the SQL Server Management Studio do the work for you!

• Bring up your table structure (right-click on the table column and select "Modify")
• Make all of your changes (if the column transformation is illegal, just add your new column - you'll patch it up in a moment).
• Right-click on the background of the Modify window and select "Generate Change Script." In the window that appears, you can copy the change script to the clipboard.
• Cancel the Modify (you'll want to test your script, after all) and then paste the script into a new query window.
• Modify as necessary (e.g. add your transformation while removing the field from the tmp table declaration) and you now have the script necessary to make your transformation.