SQL Server CURRENT_TIMESTAMP Function

If we want to get the exact time of a system on which the SQL server is currently running, we can use the CURRENT_TIMESTAMP Function provided by the SQL Server. The CURRENT_TIMESTAMP() function returns the current time-stamp of the computer in 'YYYY-MM-DD hh:mm:ss.mmm' format. The SQL Server CURRENT_TIMESTAMP Function is very useful in the scenarios when we want to log updation or deletion operation on a specific table in SQL Server.

Syntax of CURRENT_TIMESTAMP Function:

The syntax of the CURRENT_TIMESTAMP Function is as follows:

In the syntax written above, the CURRENT_TIMESTAMP is the name of the function and name_of_column can be any name that we want to give to the column under which this current timestamp will be displayed as a result of this query. The CURRENT_TIMESTAMP function takes no argument.

How to use CURRENT_TIMESTAMP Function in SQL Server?

Let us understand the usage and working of the CURRENT_TIMESTAMP Function in SQL Server with the help of an example. Let us create a table named students having three columns namely studID, rollno, and Name having datatypes as varchar, int, and varchar respectively. The syntax for creating a table with the above-mentioned schema using the CREATE TABLE command is:

CREATE TABLE students (
	studID varchar(10),
	rollNo int,
	Name varchar(30)
);

As shown in the image, we have successfully created a table having the above-mentioned schema that is three columns, two having data type as varchar and one as int.

Now, let us add some data to the students' table. The syntax of the INSERT command in SQL Server to add data in the students table will be:

INSERT INTO students(studID, rollNo, Name) VALUES(101, 87459, "Andrew");
INSERT INTO students(studID, rollNo, Name) VALUES(102, 54786, "Samuel");
INSERT INTO students(studID, rollNo, Name) VALUES(103, 22149, "Nirnay");
INSERT INTO students(studID, rollNo, Name) VALUES(104, 94365, "Paul");
INSERT INTO students(studID, rollNo, Name) VALUES(105, 35479, "Casey");
INSERT INTO students(studID, rollNo, Name) VALUES(106, 74566, "Martin");
INSERT INTO students(studID, rollNo, Name) VALUES(107, 10259, "Raphel");
INSERT INTO students(studID, rollNo, Name) VALUES(108, 85488, "Akul");

As we can see in the image, we have successfully added seven rows to the students' table and the same can be seen in the result of the SELECT query.

The next step is to create a trigger on the UPDATE command on the students table. So, whenever an UPDATE command is executed on the students' table it logs the updation records in the log table named students_log. For storing the logging information, first create a table named students_log having three columns old_data, new_data, and updation_time having datatypes as varchar, varchar, and datetime2. The syntax for creating a table with the above-mentioned schema using the CREATE TABLE command is:

 CREATE TABLE students_log (
	old_data varchar(10),
	new_data varchar(10),
	Updation_time datetime2
	
);

As we can see in the above image a table named students_log is created successfully to log the information of updation on the students table. Now let us create a trigger by the name log_updation that will get executed each time whenever an update command is executed on the students table. The syntax for the creation of trigger for the UPDATE command on the students table is:

CREATE TRIGGER log_updation ON students FOR UPDATE AS
DECLARE @new_data int;
DECLARE @old_data int;
SELECT @new_data = i.rollno from inserted i;
SELECT @old_data = o.rollno from deleted o;
BEGIN
INSERT INTO students_log(old_data,new_data,updation_time) VALUES(@old_data,@new_data, CURRENT_TIMESTAMP );
END

As we can see in the image a trigger named log_update is created successfully and this trigger will add the new, old data, and current timestamp in the students_log table. For entering the current timestamp, we have used the CURRENT_TIMESTAMP function provided by SQL Server and the syntax for entering the current timestamp into the students_log table using the CURRENT_TIMESTAMP function is:

INSERT INTO students_log(old_data,new_data,updation_time) VALUES(@old_data,@new_data, CURRENT_TIMESTAMP );

Now let us update a value in the students table and see whether data gets added in the studnets_log table or not. The syntax for updating a value in the students table is:

As we can see in the above image when we executed an UPDATE query on the students table, it did two things, first is updating the rollno column in the table and the second thing is calling the 'log_updation' trigger which entered the updated value and the previous value in the students_log table along with the time of updation using the CURRENT_TIMESTAMP function.

Now let us update some more columns in the students table and observe the output. The queries for the same are:

UPDATE students SET rollno=54771 WHERE rollno=54786;
UPDATE students SET rollno=74000 WHERE rollno=74566;
UPDATE students SET rollno=10211 WHERE rollno=10259;

As shown in the image we have executed three update queries on the students table and as an output, '1 rows affected' is displayed six times. It is displayed six times because three times for updation in the students table and three times for entry in the students_log table using the trigger that we have created on the UPDATE command for the students table.

As we can see in the image that we have successfully updated rollnos in three columns of the students table and the details of those updation queries are logged successfully in the students_log table and we can see the same that three new rows are added in the students_log table.

We can use the default keyword while entering the current timestamp in the updation_time column of the students_log table. When we will use the default keyword with the CURRENT_TIMESTAMP function then data will be inserted automatically in that column without explicating adding data in that column. The syntax for using the default keyword with the CURRENT_TIMESTAMP function is:

CREATE TABLE students_log (
	old_data varchar(10),
	new_data varchar(10),
	Updation_time datetime2 DEFAULT CURRENT_TIMESTAMP
	
);

If we use the default keyword with the CURRENT_TIMESTAMP function, then the syntax for creating a trigger on the UPDATE query on the students table to log all the updation operations on that table is:

CREATE TRIGGER log_updation ON students FOR UPDATE AS
DECLARE @new_data int;
DECLARE @old_data int;
SELECT @new_data = i.rollno from inserted i;
SELECT @old_data = o.rollno from deleted o;
BEGIN
INSERT INTO students_log(old_data,new_data) VALUES(@old_data,@new_data);
END

The difference between the previous syntax and this new syntax is in the INSERT command. In the previous, we passed three values for the three columns in the INSERT command of the studnet_log table but in this new syntax, we have passed only two values for the three columns of the studnet_log table because we have used the default keyword to add the current timestamp as the default in the updation_date column of the studnet_log table.

Instead of using the CURRENT_TIMESTAMP function, we can also use the GETDATE() Function. The GETDATE() Function is very much similar to the CURRENT_TIMESTAMP function. The only difference between the CURRENT_TIMESTAMP function and the GETDATE() function is that the CURRENT_TIMESTAMP function is an ANSI SQL function, whereas on the other hand GETDATE is a T-SQL implementation of the same function. Both of them are derive value from the operating system of the computer on which the SQL Server instance is running.

So if we use the GETDATE() Function for creating our trigger named 'log_updation' then the syntax for it will be:

CREATE TRIGGER log_updation ON students FOR UPDATE AS
DECLARE @new_data int;
DECLARE @old_data int;
SELECT @new_data = i.rollno from inserted i;
SELECT @old_data = o.rollno from deleted o;
BEGIN
INSERT INTO students_log(old_data,new_data,updation_date) VALUES(@old_data,@new_data,GETDATE());
END

The only difference between the syntax of trigger creation with the CURRENT_TIMESTAMP function and with the GETDATE() function is the parameter that we have passed for data in the updation date column of the students_log table.

SELECT CURRENT_TIMESTAMP AS date;

SELECT GETDATE() AS date;

As we can see the output of the above two queries is identical so we can say that the CURRENT_TIMESTAMP function and GETDATE() function can be used interchangeably in various SQL Server queries.

So this was one scenario where we can use the CURRENT_TIMESTAMP function or GETDATE() function, there can be any scenario where you can use these two functions provided by SQL Server.

So, this article helps us to have a better perspective of SQL Server CURRENT_TIMESTAMP function and its usage in different scenarios.

Next TopicSQL Server DATEADD Function

← prev next →