Unable to assign default constraint in an existing column in SQL Server

Question

I am using SQL Server and have a table Employee. I want to change the constraint of Starting_Date from NULL to NOT NULL and add a DEFAULT with GETDATE() function. Please check the screenshot and suggest how to solve the error I get:

Answer 1

Try this TSQL command for add, not null constraint

ALTER TABLE [dbo].[Employee] ALTER COLUMN [Starting_Date] DataTime NOT NULL;

If your table had any rows with null Starting_Date run this query before adding the constraint

UPDATE [dbo].[Employee] SET Starting_Date = [dbo].[GetDate]() WHERE Starting_Date = NULL;

and then this, for default constraint from your function output result

ALTER TABLE [dbo].[Employee]
ADD CONSTRAINT df_Starting_Date
DEFAULT([dbo].[GetDate]())FOR Starting_Date;