0

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?

Improve this question
2
  • What exact ALTER TABLE statement did you run and how big is the table (how many rows)?
    – ypercubeᵀᴹ
    Commented Dec 9, 2016 at 16:40
  • roughly 400,000 rows and the alter table statement that I ran was Alter Table bluejacks Alter Column d123 datetime
    – SmallFries BigGuys
    Commented Dec 9, 2016 at 20:24

1 Answer 1

Reset to default
0

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.