Skip to content
Related Articles

Related Articles

Improve Article

Capturing INSERT Timestamp in Table SQL Server

  • Last Updated : 17 Feb, 2021

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:

IDInDtTm
12020-12-30 13:08:54.193
22020-12-30 13:08:54.193
32020-12-30 13:10:53.163
42020-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 ;
NameID Location
Ankit234Delhi
Chetan456Noida
Khushi549Noida
Mikesh654Delhi
Varun876Noida
Kapil650Delhi

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 ;
NameIDLocationJoinDate
Ankit234Delhi2019-11-24 10:10:53.163
Chetan456Noida2018-07-14 10:12:34.163
Khushi549Noida2019-05-04 10:15:43.163
Mikesh654Delhi2018-09-21 10:13:13.163
Varun876Noida2019-10-24 10:11:33.163
Kapil650Delhi2018-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. 

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

My Personal Notes arrow_drop_up
Recommended Articles
Page :