HELP CreateDate Column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

HELP CreateDate Column

Author

Topic

tinypond
Starting Member

6 Posts

Posted - 2008-04-29 : 15:57:55

I am new to SQL SERVER and am running into a brick wall

Example table foo
Column CreateDate, smalldatetime

I would like to have this column populated with the systemdatetime on creation but never again.

Would it be best to create a trigger and after insert store the value returned by getdate() in the column foo.createDate?

if so what would the syntax for creation of this trigger look like?

Is there another method that would be easier. Example my take on constraints would be that the createDate column would be replaced both on insert and update.

Please help, thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-29 : 16:01:30

Just create a default constraint on the column and set it to GETDATE().

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tinypond
Starting Member

6 Posts

Posted - 2008-04-29 : 16:14:46

would this not change each time the record is updated? I only want it to be populate on initial creation

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-29 : 16:16:21

Here is what the SQL Server documentation says about defaults:

quote:

Defaults specify what values are used in a column if you do not specify a value for the column when you insert a row.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tinypond
Starting Member

6 Posts

Posted - 2008-04-29 : 16:20:45

What would be recommended if someone wanted the column to store the latest datetime the row was updated?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-29 : 16:35:28

Just pass GETDATE() to the column in the UPDATE statement.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tinypond
Starting Member

6 Posts

Posted - 2008-04-29 : 16:40:11

thank you

tinypond
Starting Member

6 Posts

Posted - 2008-04-29 : 17:57:19

Here is an additonal question. If I have to send the getdate() function in the update statement is not what I need because if someone updates this via sql then I don't get the modifydate. I want the database to insure intergrity here. Please help..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-29 : 18:02:06

Then you need to do it from a trigger.

UPDATE t
SET DateTimeColumn = GETDATE()
FROM YourTable t
INNER JOIN inserted i
ON t.SomeColumn = i.SomeColumn

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tinypond
Starting Member

6 Posts

Posted - 2008-04-29 : 23:45:29

YES! Thank you and now I know why they call you the "Almighty SQL Goddess!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources