DEFAULT Date column value

For modifying an existing column in an existing table:

ALTER TABLE YourTable ADD CONSTRAINT DF_YourTable DEFAULT GETDATE() FOR YourColumn