sql - How to alter and add a datetime2(7) not null field to an already existing table with data?

Question

I have table that already has a set of values. Now I have to add a column thats of type datetime2(7) which will be not null. I cannot insert getdate() as the default value as this can lead to problems. How can I add that column with 0001-01-01 00:00:00.0000000 as the default value?

question from:https://stackoverflow.com/questions/65842524/how-to-alter-and-add-a-datetime27-not-null-field-to-an-already-existing-table

Answer 1

Define the DEFAULT value and use WITH VALUES:

ALTER TABLE dbo.YourTable ADD NewColumn datetime2(7) NOT NULL DEFAULT '0001-01-01T00:00:00.0000000' WITH VALUES;

Though, a default value of 0001-01-01T00:00:00.0000000 is pretty odd. Seems like, in my opinion, it should be NULL. NULL means an unknown value, and clearly you don't know what the value of these rows should be.