Create Field With Default Value Of Different Field With No Data Loss 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?
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
 GO


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