Capturing INSERT Timestamp in Table SQL Server

While inserting data in tables, sometimes we need to capture insert timestamp in the table. There is a very simple way that we could use to capture the timestamp of the inserted rows in the table.

Capture the timestamp of the inserted rows in the table with DEFAULT constraint in SQL Server.

Use DEFAULT constraint while creating the table:

Syntax:

CREATE TABLE TableName (ColumName INT, ColumnDateTime DATETIME DEFAULT CURRENT_TIMESTAMP)
GO

Example:

Let’s create a table named ‘GeekTab’.

CREATE TABLE GeekTab (ID INT, InDtTm DATETIME DEFAULT CURRENT_TIMESTAMP) ;
GO

Let’s insert few values in the table.

INSERT INTO GeekTab (ID) VALUES (1),  (2) ; 
GO

INSERT INTO GeekTab (ID)VALUES (3), (4) ; 
GO

Now, let’s select the value from the table.

SELECT * FROM GeekTab ;
GO

Output:

ID	InDtTm
1	2020-12-30 13:08:54.193
2	2020-12-30 13:08:54.193
3	2020-12-30 13:10:53.163
4	2020-12-30 13:10:53.163

Conclusion:

When we select the value from the table GeekTab, we could see that it is having the current timestamp in the table in InDtTm column. This is because the column InDtTm was created with DATETIME DEFAULT CURRENT_TIMESTAMP.

Scenario-based example:

Let us suppose we have table “EmployeeTab”, whenever a new employee joins the company, a new record is inserted in the table:

CREATE TABLE EmployeeTab (Name varchar(100), ID INT, Location varchar(100),
InDtTm DATETIME DEFAULT CURRENT_TIMESTAMP); 

SELECT  Name, ID, Location FROM EmployeeTab ;

Name	ID	Location
Ankit	234	Delhi
Chetan	456	Noida
Khushi	549	Noida
Mikesh	654	Delhi
Varun	876	Noida
Kapil	650	Delhi

To get the insert date and time for each entry as ‘JoinDate’ below query could be used:

SELECT  Name, ID, Location, InDtTm as JoinDate FROM EmployeeTab ;

Name	ID	Location	JoinDate
Ankit	234	Delhi	2019-11-24 10:10:53.163
Chetan	456	Noida	2018-07-14 10:12:34.163
Khushi	549	Noida	2019-05-04 10:15:43.163
Mikesh	654	Delhi	2018-09-21 10:13:13.163
Varun	876	Noida	2019-10-24 10:11:33.163
Kapil	650	Delhi	2018-12-14 10:10:53.163

Note: There is one exception while using this feature and that if anyone inserts the explicit value inside the column ColumnDateTime, the column would have the newly inserted value and not the default value. 

My Personal Notes arrow_drop_up

Save