Open In App

Capturing INSERT Timestamp in Table SQL Server

Improve
Improve
Like Article
Like
Save
Share
Report

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. 


Last Updated : 17 Feb, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads