Insert Date

  • Hello:

    I have a table that i want to add a field, say, Insertdate, that would only be populated whenever a new record was inserted, not for updates.  I can easily add the field using Enterprise Manager, and define it as timestamp.  But how do I ensure it will only be populated on INSERTS only ?

    Thanks

     

  • Well if you want set a default date GETDATE() for the column at the time of definition, which will automatically populate with the system date on Insert, irrespective of whether you are referencing it in your insert or update statements.

    If you are using stored procedures for Insert and Update , don't just include the column in your update statement, I am not sure If I am missing anything here.

    Thanks

     

    Prasad Bhogadi
    www.inforaise.com

  • Alternatively you can use this:

    alter table yourtable

     add InsertDate datetime default getdate()

    go

     

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Thank you for the info.

  • Alternatively, you can define an INSERT trigger to set the insertdate.

  • Miller Time,

    Don't define the new column as a TimeStamp datatype... the TimeStamp data type in MS-SQL Server is actually a binary number used for row versioning and has nothing to do with dates or times.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Yeah, I defined it as a datetime field.  Works great !   Only gets populated with an Insert, never modified after that.  Thanks all for the good help !

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply