How to Use CURRENT_TIMESTAMP Function in SQL Server

by

If the database developer wants to get the exact time of a system on which the SQL server is currently running, they can use the CURRENT_TIMESTAMP Function provided by SQL Server.

The CURRENT_TIMESTAMP() function returns the computer’s current time stamp in ‘YYYY-MM-DD hh:mm: ss.mmm’ format. This function is useful when we want to record additions or deletions to a particular SQL Server table.

Continue reading this tutorial on how to use the current_timestamp function in SQL Server.

CURRENT_TIMESTAMP Function in SQL Server

CURRENT_TIMESTAMP retrieves the current timestamp (date and time value) of the system on which the SQL Server instance runs. This function retrieves the data and time value of the local time zone servers.

The syntax is given below.

CURRENT_TIMESTAMP

Where,

  • CURRENT_TIMESTAMP: It is a function that doesn’t accept any parameter, and the return type of this function is datetime datatype, which contains data and time down to fractions of seconds.

For example, let’s know the current system time of where SQL Server is currently running. For that, use the below query.

SELECT CURRENT_TIMESTAMP AS CurrentDateAndTime;
CURRENT_TIMESTAMP Function in SQL Server

From the above output, you can see the datetime value returned by the CURRENT_TIMESTAMP function is 2023-11-21 09:57:48.967.

It returns the timestamp value in the format ‘YYYY-MM-DD hh:mm:ss.nnn’, which is the standard SQL format for datetime data types.

Where,

  • YYYY: It represents the four-digit year, for example, 2023.
  • MM: It represents the two-digit month, for example, 11.
  • DD: It represents the two-digit day, for example, 21.
  • hh: It represents the two-digit hour in 24-hour format, for example, 09 hours.
  • mm: It represents the two-digit minutes, for example, 57 minutes.
  • ss: It represents the two-digit seconds, for example, 48 seconds.
  • nnn: It represents the fractional seconds (milliseconds), which can be three decimal places long, for example, 967 fractional seconds.

The above is the format in which CURRENT_TIMESTAMP returns the system time (timestamp) in SQL Server.

Now move, and let’s see how to use the CURRENT_TIMESTAMP function in SQL Server with table.

CURRENT_TIMESTAMP Function in SQL Server with Table Column

You can use the CURRENT_TIMESTAMP function with the table column as the default value.

For example, you can set the CURRENT_TIMESTAMP as the default value for the column, so whenever the data is inserted into the table, the time of insertion should be recorded in the database.

To understand this, use the query below to create a table AuditLog with columns AuditID, UserID, Action, and ActionTime.

CREATE TABLE AuditLog (
	AuditID INT IDENTITY(1,1) PRIMARY KEY,
	UserID INT,
	Action VARCHAR(50),
	ActionTime DateTime DEFAULT CURRENT_TIMESTAMP
	);

When you create the above AuditLog table, it creates a column AuditID (a primary key that automatically increases its value based on the insertion of the records), UserID, Action (describe the user’s action), and the ActionTime column, the time of action when it is performed and defaults to the current timestamp.

So you can see that the ActionTime column is set to CURRENT_TIMESATMP, which is the default in this case. So, whenever a record is inserted into an AuditLog table, this column records the timestamp of that insertion.

Insert the five records into the AuditLog table using the below query.

INSERT INTO AuditLog(UserID, Action) VALUES(234,'Login');
INSERT INTO AuditLog(UserID, Action) VALUES(235,'Logout');
INSERT INTO AuditLog(UserID, Action) VALUES(236,'Change Password');
INSERT INTO AuditLog(UserID, Action) VALUES(237,'Update Profile');

View the AudtiLog table using the below query.

Select * From AuditLog;
CURRENT_TIMESTAMP Function in SQL Server with Table Column

From the above picture, which shows the output of each query you have executed, the result contains each user action with the time whenever these actions are performed.

For example, the user with an ID equal to 234 performed the login action on the date 2023-11-21 at time 10:42:19.120. But here, you see the same ActionTime (datetime) value for each user because we have executed the four records simultaneously.

If you insert the above four records one by one, you will see the difference in the ActionTime of each user.

Well, now you clearly understand how to use the CURRENT_TIMESTAMP function in SQL Server to get the current system time of the SQL Server wherever it is running.

Conclusion

In this SQL Server tutorial, you learned how to get the current timestamp value of the system using the CURRENT_TIMESTAMP function in SQL Server. Also, while creating the table, you defined the column default value as CURRENT_TIMESTAMP and inserted the data into that table to record the time of change.

You may like to read: