Add default value of datetime field in SQL Server to a timestamp

In that table in SQL Server, specify the default value of that column to be CURRENT_TIMESTAMP. The datatype of that column may be datetime or datetime2.

e.g.

Create Table Student
(
  Name varchar(50),
  DateOfAddmission datetime default CURRENT_TIMESTAMP
);

This can also be done through the SSMS GUI.

1. Put your table in design view (Right click on table in object explorer->Design)
2. Add a column to the table (or click on the column you want to update if it already exists)
3. In Column Properties, enter (getdate()) in Default Value or Binding field as pictured below

For modifying an existing column in an existing table:

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

While the marked answer is correct with:

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

You should always be aware of timezones when adding default datetime values in to a column.

Say for example, this datetime value is designed to indicate when a member joined a website and you want it to be displayed back to the user, GETDATE() will give you the server time so could show discrepancies if the user is in a different locale to the server.

If you expect to deal with international users, it is better in some cases to use GETUTCDATE(), which:

Returns the current database system timestamp as a datetime value. The database time zone offset is not included. This value represents the current UTC time (Coordinated Universal Time). This value is derived from the operating system of the computer on which the instance of SQL Server is running.

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

When retrieving the values, the front end application/website should transform this value from UTC time to the locale/culture of the user requesting it.