How can I store/retrieve a timestamp in SQL Server?

Question

I have a table where each row needs a timestamp marking the time it has been inserted. I don't have a lot of database experience, but something tells me that this would best be handled automatically on the database side, for example through a stored procedure.

There is a timestamp datatype in MS SQL, but reading about it, it is treated as a series of numbers and not as a DateTime object in C#. So couldn't it just be a NVARCHAR datatype if I have to do the conversion anyway?

So simply put, what is the easiest way to (automatically?) put a timestamp on a row when it is inserted into a table?

Edit: the table definition is very simple. It only has two columns at the moment and still lacking the timestamp column.

        SqlCommand command = con.CreateCommand();
        command.CommandText = "INSERT INTO Bio VALUES (" +
            "@ID, @Name)";

        command.Parameters.AddWithValue("ID",number);
        command.Parameters.AddWithValue("Name", name);

        try
        {
            SqlDataReader thisReader = command.ExecuteReader();
            thisReader.Close();
        }
        catch { // Do something };

Solution Okay, it works now. I first tried adding the new column through the Server Explorer window in Visual Studio 2008. Finding my database, right click and selecting "New Query". Then typing something like

ALTER TABLE dbo.MyTable ADD CreatedDate datetime NOT NULL DEFAULT (GetUtcDate())

But I got the message that

The ALTER TABLE SQL construct is not supported The query cannot be represented graphically in the Diagram and Criteria Pane.

So instead I added it graphically. First "Open table definition" and adding CreatedDate under Column Name and datetime under Data Type. Removing the Allow Nulls check. Then in the Column Properties I added

getutcdate()

under the General section in the row called "Default Value or Binding". Whenever a new row is inserted, the database automatically gives it the current date and time as a timestamp.

Solution 1

to have a CreatedDate column in a table create it like this:

ALTER TABLE dbo.MyTable ADD CreatedDate datetime NOT NULL DEFAULT (GetUtcDate())

Edit: if afterwards you want to have the value updated at every update of that record you can reassign GetUtcDate() to that column or to another one (I called it Createddate so should not be used in the update, but you can either add another one or rename it Modifieddate). I would definitely include the update of such column in the stored procedure which does the UPDATE, no need for triggers here.

Solution 2

The SQL Server "timestamp" type is not the same as the standard "timestamp": it is more correctly "rowversion". The datatype "timestamp" is being deprecated. See MSDN for all of this

You should use datetime which is a real date/time value

The easiest way is to have a DEFAULT on the column with GETDATE or GETUTCDATE on the table so that SQL Server maintains it automatically

Edit: for updates

You can use this syntax for updates

UPDATE T SET col = @x, UpdatedDateTime = DEFAULT WHERE ...

This is useful because the default constraint determines the value not the code. So if you change the default (say you switch to GETUTCDATE from GETDATE) then you change a constraint, not code

Solution 3

You can use CURRENT_TIMESTAMP or FN NOW() to insert time stamp into a row.

Solution 4

Creating a trigger is one way, although probably not the best way, to handle this on the SQL side if you also want to update the timestamp when a row is updated:

CREATE TRIGGER MyTimestampTrigger ON tblMyTable FOR INSERT,UPDATE AS
UPDATE tblMyTable SET fldTimestamp=getdate() WHERE fldId IN (SELECT fldId FROM INSERTED)
GO

(If fldId is the primary key, fldTimestamp is the timestamp field and tblMyTable the name of the table).

The other answers with a default value on a datetime column is probably your best choice however.

View more solutions

Comments

[Kasper Hansen]

I tried to use this command line, but found the graphical editor easier. What I did is added to the bottom of the original question. Thank you for the help.

[Tom]

I know this is a really old question, but fantastic answer!