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:
CREATE TABLE TableName (ColumName INT, ColumnDateTime DATETIME DEFAULT CURRENT_TIMESTAMP) GO
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
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
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.
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 ;
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 ;
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. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.