In this SQL Server tutorial, you will learn about the CURRENT_TIMESTAMP function in SQL Server that helps in finding the current date and time of the system.
You will learn about the syntax of CURRENT_TIMESTAMP with an example and then how to use the CURRENT_TIMSTAMP function with a table column.
You will create a table and insert the data into that table to record the time of the data insertion.
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: It is a function that doesn’t accept any arguments, and the return type of this function is datetime datatype, which contains data and time down to fractions of seconds.
The CURRENT_TIMESTAMP returns a different value whenever it is called, which means it returns the exact date and time of that moment. It is called a non-deterministic function because its output changes with each call of this function.
- If this function is non-deterministic, then indexing views or expressions that include this function is impossible.
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, 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.
- 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,
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;
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.
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:
- How to use GETDATE Function in SQL Server
- CAST Function in SQL Server
- SYSDATETIMEOFFSET Function in SQL Server
After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.