How to Use CURRENT_TIMESTAMP Function in SQL Server?

As a database developer, I was working on the query and wanted to get the exact time of a system on which the SQL server is currently running; then, I could use the CURRENT_TIMESTAMP Function provided by the 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 recording additions or deletions to a particular SQL Server table.

Now, let me show you 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: This function doesn’t accept any parameters. Its return type is the 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;

From the above output, the datetime value returned by the CURRENT_TIMESTAMP function is 2024-07-09 15:33:23.873.

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, 2024.
• MM: It represents the two-digit month, for example, 07.
• DD: It represents the two-digit day, for example, 09.
• hh: It represents the two-digit hour in 24-hour format, for example, 15 hours.
• mm: It represents the two-digit minutes, for example, 33 minutes.
• ss: It represents the two-digit seconds, for example, 23 seconds.
• nnn: It represents the fractional seconds (milliseconds), which can be three decimal places long, for example, 873 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.

Read Also How To Convert Int to Fixed Length String in SQL Server

CURRENT_TIMESTAMP Function in SQL Server with Table

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, 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.

You can see that the ActionTime column is set to CURRENT_TIMESATMP, the default in this case. This column records the timestamp of every record inserted into an AuditLog table.

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;

The above output shows the output of each query you have executed. The result contains each user action and the time when these actions were performed.

For example, the user with an ID equal to 234 performed the login action on the date 2024-07-09 at time 15:38:23.690. 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.

Let’s look at the other interesting table, OrderDetail, in which we inserted the values individually.

CREATE TABLE OrderDetail (
Order_Id INT ,
Product_Id INT NOT NULL,
Product_Name VARCHAR(20) NOT NULL,
Deliveredtime Datetime Default Current_Timestamp NOT NULL
);

INSERT INTO OrderDetail (Order_Id, Product_Id, Product_Name, Deliveredtime)
VALUES (104, 6007,'HP',CURRENT_TIMESTAMP );

Select * from OrderDetail 

Here in the output, you can see the variation for the current time in both columns.

Alternate to Current Time_Stamp in SQL Server

Alternatively, we can use the GETDATE() function instead of CURRENT_TIMESTAMP. The GETDATE() function is similar to the CURRENT_TIMESTAMP function, the only differenece is that CURRENT_TIMESTAMP is an ANSI SQL function, while GETDATE is a T-SQL implementation of the same function.

Both functions derive their values from the operating system of the computer hosting the SQL Server instance.

This is how we can use GETDATE() function in SQL Server.

Conclusion

Well, I hope 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.

Also, while creating the table, you define the column default value as CURRENT_TIMESTAMP and insert the data into that table to record the time of change.

You may like to read:

• How to use GETDATE Function in SQL Server
• CAST Function in SQL Server
• SYSDATETIMEOFFSET Function in SQL Server