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.

Mehitable

Answer 1

You need to set the "default value" for the date field to getdate(). 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 in the column properties tab if you select the date field of your table when editing the table.

Arnone

I've changed the field to DateTime2(7) and put GETDATE() in to the default value. However, when a view the table contents and try to ender in data I'm receiving a 'cannot contain null error' when tabbing out of the DateTime field - obviously I haven't put anything into it as I expected it to be automatically updated. Also, I'm using Visual Web Developer 2008 Express Edition (which I believe uses the SQL Express database package). – Mehitable

@Mike - make the column nullable as well and then it should work – Vanscoy

I found the problem - I hadn't set my ID column to Identity so the huge message was actually pointing me to my ID column being incorrect and not my DateTime. I have learnt. Apologies. But it works. Many thanks. – Mehitable

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

Emblaze

Thanks, but I'm really at the beginning of my SQL experience, so I'm using the IDE to help me make a table. – Mehitable

Answer 3

Just right click on that column and select properties and write getdate()in Default value or binding.like image:

If you want do it in CodeFirst in EF you should add this attributes befor of your column definition:

[Databasegenerated(Databaseoption.computed)]

this attributes can found in System.ComponentModel.Dataannotion.Schema.

Decant