Sql-server – Create Field With Default Value Of Different Field With No Data Loss

sql serversql-server-2008-r2t-sql

I have a datetime2 field that I need to be a datetime field. If I try to run an Alter Table statement my SSMS session freezes and I have to force reboot with no change made, if I try to alter my design view of the table I get an error about needing to drop/re-create the table.

Which leads me to my question, if I have a field that is datetime2 if I Add Column helper1 datetime how could I set the default value of this column to dt2 which is a datetime2 data type?

Best Answer

You're looking for a computed column if you want to keep the column with datetime2 data.

 ALTER TABLE Your-Table 
 ADD helper1 AS 
          CAST(Datetime2-Column AS datetime)

If you want to completely change the column, you can add a new column, transfer the data, then drop the old column and re-name the new column to the name of the old column. Steps below:

 ALTER TABLE Your-Table ADD Datetime2-Column DATETIME2

 UPDATE Your-Table
 SET Datetime-Column = Datetime2-Column

Then drop datetime2 column.

ALTER TABLE Your-Table DROP COLUMN Datetime2-Column

Then re-name your datetime column to your original datetime2 column's name. You can do this in Management Studio or with sp_rename