Presenting a technical session is a greatest experience one can have and I enjoy doing the same. While I write this blog post, I am presenting at Great Indian Developer Summit in India. The event is a grand success and I am having a great time at this event. One of the questions which I often receive is how do one can add the column to existing table which will be auto-populated with the current datetime when the original row is inserted. There is indeed a simple solution to achieve this goal. One has to just create table with default value as a current datetime.
In following example we will first create a sample table and later we will add a column which will be defaulted to the current date time when any new record is inserted. The only drawback of this method is that if there is any existing row in your table it will be automatically have the current date time when the column is created. Honestly I do not see any solution to this issue as this is related to design of the database. If you know what was the datetime when rows were created you can update those rows with those value otherwise, just have any values stored there.
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
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
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
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
Here is the question back to you – “It is simple to create a column where we have default daytime value to know when the row was created. Is there any way to know when the row was updated without explicitly updating any column with datetime?”
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
31 Comments. Leave new
Is there any way to know when the row was updated without explicitly updating any column with datetime?
This is intresting question,
we can use rowversion to track changes & maintain integrity,
but I think we need explicitly update to maintain modified date.
do the same with a GUID;
ADD MyGUID UNIQUEIDENTIFIER NOT NULL DEFAULT (NEWID());
might want to mention GETUTCDATE() as the default , working across time zones working with utc date times can help with a lot of issues that come up
What is faster? Getdate() or CURRENT_TIMESTAMP?
Getdate() is inherited from Sybase. CURRENT_TIMESTAMP is ANSI compliant. According to BOL, getDate () and CURRENT_TIMESTAMP performs exactly the same.
How to get current field value as default depending on other field for ex if primary key value id=3 then prodno =prod-3
How to get current field value as default depending on other field for ex if primary key value id=3 then prodno =prod-3 value should automatic generate and should be inserted on insert operation
Hi Utkarsh,
you have to use triggers or make it a calculated attribute. If you want to index the attribute add the PERSISTED property to the attribute
e.g.
ALTER TABLE dbo.yourtable
ADD [CalcAtt] AS [Id – 3] PERISTED;
aargh – typo :(
ALTER TABLE dbo.yourtable ADD [CalcATT] AS [Id] – 3 PERSISTED;
how to write to get createdby or modifiedby column autofilled to get the current user logged in i.e. who ever runs the insert query
useful information.. thanks it helps me.
If you have previous rows (let’s say thousands!!!), adding a date field to the table will fill all rows with zeros (0000-00-00), and in my case, I am using asp.net, this throws exceptions ALWAYS.
The work around I use is: add the date field and set default to 0001-01-01. This will fill all previous rows with a correct value, which is obviously a “dummy” date.
You will then adjust your code to intercept these date values and update them.
Pinal, your question is still unanswered. can you please put your thoughts?
What do you have in mind?
This is the MySQL query which i created please help me in creating the same by MSSQL server 2008
CREATE TABLE `subjects` (
`subject_id` int(11) NOT NULL AUTO_INCREMENT,
`subject_name` varchar(100) NOT NULL,
`elective_or_optional_subject` bit(1) NOT NULL,
`branch_id` int(11) NOT NULL,
`created_by` int(11) DEFAULT NULL,
`modified_by` int(11) DEFAULT NULL,
`created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`modified_date` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
`active` bit(1) DEFAULT NULL,
PRIMARY KEY (`subject_id`),
KEY `branch_id` (`branch_id`),
KEY `created_by` (`created_by`),
KEY `modified_by` (`modified_by`),
CONSTRAINT `subjects_ibfk_1` FOREIGN KEY (`branch_id`) REFERENCES `branch_type` (`branch_id`),
CONSTRAINT `subjects_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `user` (`id`),
CONSTRAINT `subjects_ibfk_3` FOREIGN KEY (`modified_by`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
please reply sir
Is there any one to reply on my question please
CREATE TABLE subjects (
subject_id int identity(1,1) PRIMARY KEY NOT NULL,
subject_name varchar(100) NOT NULL,
elective_or_optional_subject bit NOT NULL,
branch_id int NOT NULL,
created_by int DEFAULT NULL,
modified_by int DEFAULT NULL,
created_date datetime NOT NULL DEFAULT getdate(),
modified_date timestamp NULL DEFAULT getdate(),
active bit DEFAULT NULL);
Create index idx_subjects_branch_id on subjects(branch_id);
Create index idx_subjects_created_by on subjects(created_by);
Create index idx_subjects_modified_by on subjects(modified_by);
you need to define constraint based on other tables.
Is it possible to insert a row automatically based on specific time(ex: insert a row every day 9am in a table) with out user interaction. please suggest me
Someone has do so that either user or computer. You can do that via creating job in SQL Server Agent which can do “scheduled” activity.
Hi Pinal –
This question is still open. Can you help us understand different approaches to know when the row was updated without explicitly updating any column with datetime?
Thanks in advance!!
thankx, helped me
@Pinal.. What will be the default time zone for the default date time field? Time zone of the server/datacenter?
how to add a field with datetime in Hour-Minute-Second-MM-DD-YYYY format
Is it possible to set the column default value only to date without time? Means if I set getdate() in the ‘DefaultValue or Binding’ of Column properties, this automatically includes time as well. I don’t want to include time. Is there any way to ignore time?
it is not working