Making a DateTime field in a database automatic?

database database-design datetime sql-server-express field
RocketGoal picture RocketGoal · Mar 16, 2010 · Viewed 99k times · Source

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

Ant picture Ant · Mar 16, 2010

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 if you select the date field of your table when editing the table.

Related questions

Best way to store time (hh:mm) in a database

I want to store times in a database table but only need to store the hours and minutes. I know I could just use DATETIME and ignore the other components of the date, but what's the best way to do …

Read More
Can I have multiple primary keys in a single table?

Can I have multiple primary keys in a single table?

Read More
Database, Table and Column Naming Conventions?

Whenever I design a database, I always wonder if there is a best way of naming an item in my database. Quite often I ask myself the following questions: Should table names be plural? Should column names be singular? Should …

Read More