add not null column take values from other column

I have an existing table say, Items with Id | CreateDate (as DateTime) columns; want to add Year int not null, take the default year from the CreateDate column.

The Year value will be futer updated to different value, but just set it as default value when the column Year is added, because want to add non-nullable column to Year.

Is that possible?

Answer

If you want to add a new column and initialize it with the values from the other one, you can do:

-- add the column (as nullable)
alter table mytable add created_year int;

-- update the values on existing rows
update items set created_year = year(created_date);

-- make the column not nullable
alter table mytable alter column created_year int not null;