How to add a default value to an already existing column?

Question

I have an existing column in my SQL Server database. I have tried about everything I can think of but can not get a default value to be added to the column. What works in every other database is

alter table mytable    alter column mycolumn set default(now()) --mycolumn is a datetime 

How do I do this in SQL Server?

The error I get for that exact syntax is incorrect syntax near the keyword 'set'

Answer 1

Use:

ALTER TABLE dbo.mytable ADD CONSTRAINT def_mycolumn DEFAULT GETDATE() FOR mycolumn 

For more info, see: Working with Default Constraints

Answer 2

If you want to change the default value of an already existing column. You need to first drop the constraint and then add the constraint all over again as below

ALTER TABLE <TABLE>  DROP CONSTRAINT <CONSTRAINT NAME>  ALTER TABLE <TABLE>  ADD CONSTRAINT <CONSTRAINT NAME> DEFAULT <VALUE> for <COLUMN> 

If you are not having the Constraint details of the table, you can use the below query

sp_helpconstraint <TABLE>