Adding Column Defaulting to Current Datetime in Table

Let us see our solution. Let us first create a table which does not have column with current datetime. In our case we will assume that there are only two rows in the table.

USE tempdb
GO
-- Create Table
CREATE TABLE TestTable (ID INT, Col1 VARCHAR(100));
-- Insert Values
INSERT INTO TestTable (ID, Col1)
SELECT 1, 'First'
UNION ALL
SELECT 2, 'Second';
-- Select from table
SELECT *
FROM TestTable
GO

e9ee14e233 DateInserted1

Now let us add a column to this table with default value as a current datetime. You will notice that the two rows which are inserted into the table have current datetime.

-- Add Column with Default Current Date Time
ALTER TABLE TestTable
ADD DateInserted DATETIME NOT NULL DEFAULT (GETDATE());
-- Select from table
SELECT *
FROM TestTable
GO

1bf078073b DateInserted2

As a third step let us enter the a new row. Make sure that you do not insert any value in the newly created column where you have default value as a current date time.

-- Now Insert New Rows
INSERT INTO TestTable (ID, Col1)
SELECT 3, 'Third';
INSERT INTO TestTable (ID, Col1)
SELECT 4, 'Fourth';
GO
-- Select from table
SELECT *
FROM TestTable
GO

1bf078073b DateInserted3

You will notice in the result set that the new column will contain current date time of the row created. This way you can get the value when the row was created.

Now you can clean up the resultset.

-- Clean up
DROP TABLE TestTable
GO


Discover more from CODE t!ps

Subscribe to get the latest posts sent to your email.

Scroll to Top

Discover more from CODE t!ps

Subscribe now to keep reading and get access to the full archive.

Continue reading