How to record the current date/time value in SQL Server?

Say we have the following table in SQL Server recording incoming payments:

CREATE TABLE Payment
(
   PaymentId INT NOT NULL IDENTITY (1,1) PRIMARY KEY,
   PaymentType VARCHAR(15) NOT NULL,
   Amount DECIMAL(6,2),
   PaymentDate DATETIME NOT NULL
)

We would like to record in PaymentDate the date and time value each payment record is inserted into the table.

One way to do so is to use the current date/time timestamp when a new record is inserted into the table. So, inserting a new record could look like this:

INSERT INTO Payment (PaymentType, Amount, PaymentDate)
VALUES
('Payment Owed', 20.12, GETDATE()),
('Gift', 15.47, GETDATE())

This INSERT statement uses GETDATE built-in function, which

Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running.

The table after this INSERT takes place looks like:

PaymentId	PaymentType	Amount	PaymentDate
1	Payment Owed	20.12	2017-05-28 09:24:12.237
2	Gift	15.47	2017-05-28 09:24:12.237

A more convenient way around this problem would be to add a default constraint to PaymentDate column:

ALTER TABLE Payments
ADD CONSTRAINT df_CurrentDateTime
DEFAULT CURRENT_TIMESTAMP FOR [Payment_Date]

The constaint is just a default value for column PaymentDate. This value is the value returned by built-in function CURRENT_TIMESTAMP:

This function is the ANSI SQL equivalent to GETDATE.

So, the function does the same thing with GETDATE. If we add the constraint, the INSERT statement is simplified to:

INSERT INTO Payment (PaymentType, Amount)
VALUES
('Payment Owed', 20.12),
('Gift', 15.47)