SQL cant create column for TIMESTAMP for SQL server 2014

Question

I'm having trouble creating a column in a table with the following query:

alter table Items 
   add ModifiedTime timestamp not null
       default current_timestamp on update current_timestamp;

I get this error:

Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'on'.

I don't know why, I have tried different ways to write the query but cannot get the "ON UPDATE" part to work.

Also I tried just adding the column using this query:

alter table Items 
   add ModifiedTime timestamp not null
       default current_timestamp

And that query works, but the value showing in column ModifiedTime is totally wrong, its saying: 0x0000000000002713. I have no idea why its saying that either..

I'm using Microsoft SQL Server 2014 Management Studio, and the SQL Server is SQL Server Express 64bit , version 12.0.2000.8 if that helps anything

Answer 1

FIrst of all - TIMESTAMP in T-SQL has nothing to do with a regular date & time - it's a binary row version indicator, really (see the relevant TechNet documentation for it - it's now called ROWVERSION).

If you want to track date & time, use DATETIME2(n) (with n being the after-seconds comma precision needed, 3 = milliseconds - values from 0 to 7 are allowable)

Secondly - the syntax you're using (the on update current_timestamp; part of it) is not valid T-SQL syntax. There's no declarative way in T-SQL to define a column being updated when the row is changed - if you want to keep track of the "last modified date", you need a trigger.

Update:

Your table would have to look something like

CREATE TABLE dbo.Items
(
     ItemsID INT IDENTITY(1,1) NOT NULL
         CONSTRAINT PK_Items PRIMARY KEY CLUSTERED,

     ....(some other columns)....

     CreatedDate DATETIME2(3)
         CONSTRAINT DF_Items_CreatedDate DEFAULT (SYSDATETIME()),
     ModifiedDate DATETIME2(3)
         CONSTRAINT DF_Items_ModifiedDate DEFAULT (SYSDATETIME())
)

and then you'd need a trigger

CREATE TRIGGER trgItems_Update
ON dbo.Items
AFTER UPDATE
AS
    UPDATE it
    SET ModifiedDate = SYSDATETIME()
    FROM dbo.Items it
    INNER JOIN Inserted i ON it.ItemsID = i.ItemsID