Making a DateTime field in a database automatic?

Question

I'm putting together a simple test database to learn MVC with. I want to add a DateTime field to show when the record was CREATED.

ID = int
Name = Char
DateCreated = (dateTime, DateTime2..?)

I have a feeling that this type of DateTime capture can be done automatically - but that's all I have, a feeling. Can it be done? And if so how?

While we're on the subject: if I wanted to include another field that captured the DateTime of when the record was LAST UPDATED how would I do that.

I'm hoping to not do this manually.

Many thanks

Mike

Answer 1

You need to set the "default value" for the date field to "getdate()" (without quotes). Any records inserted into the table will automatically have the insertion date as their value for this field.

The location of the "default value" property is dependent on the version of SQL Server Express you are running, but it should be visible if you select the date field of your table when editing the table.

Answer 2

Yes, here's an example:

CREATE TABLE myTable ( col1 int, createdDate datetime DEFAULT(getdate()), updatedDate datetime DEFAULT(getdate()) )

You can INSERT into the table without indicating the createdDate and updatedDate columns:

INSERT INTO myTable (col1) VALUES (1)

Or use the keyword DEFAULT:

INSERT INTO myTable (col1, createdDate, updatedDate) VALUES (1, DEFAULT, DEFAULT)

Then create a trigger for updating the updatedDate column:

CREATE TRIGGER dbo.updateMyTable 
ON dbo.myTable
FOR UPDATE 
AS 
BEGIN 
    IF NOT UPDATE(updatedDate) 
        UPDATE dbo.myTable SET updatedDate=GETDATE() 
        WHERE col1 IN (SELECT col1 FROM inserted) 
END 
GO