Alter column, add default constraint

Question

I have a table and one of the columns is "Date" of type datetime. We decided to add a default constraint to that column

Alter table TableName
alter column dbo.TableName.Date default getutcdate()

but this gives me error:

Incorrect syntax near '.'

Does anyone see anything obviously wrong here, which I am missing (other than having a better name for the column)

user_740 · Answer

Try this

alter table TableName 
 add constraint df_ConstraintNAme 
 default getutcdate() for [Date]

example

create table bla (id int)

alter table bla add constraint dt_bla default 1 for id



insert bla default values

select * from bla

also make sure you name the default constraint..it will be a pain in the neck to drop it later because it will have one of those crazy system generated names...see also How To Name Default Constraints And How To Drop Default Constraint Without A Name In SQL Server

user_131688 · Answer

you can wrap reserved words in square brackets to avoid these kinds of errors:

dbo.TableName.[Date]

user_15183618 · Answer

I confirm like the comment from JohnH, never use column types in the your object names! It's confusing. And use brackets if possible.

Try this:

ALTER TABLE [TableName]
ADD  DEFAULT (getutcdate()) FOR [Date];

user_2938266 · Answer

You're specifying the table name twice. The ALTER TABLE part names the table. Try: Alter table TableName alter column [Date] default getutcdate()

user_8932378 · Answer

alter table TableName drop constraint DF_TableName_WhenEntered

alter table TableName add constraint DF_TableName_WhenEntered default getutcdate() for WhenEntered