CODEWITHSUNDEEP
sqltimestampsql-server-2014-express
Mikael
Mikael

Reputation: 35

SQL cant create column for TIMESTAMP for SQL server 2014

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

Upvotes: 1

Views: 2969

Answers (3)

Alireza Askari Kochi
Alireza Askari Kochi

Reputation: 1

To add a RowVersion column to your table in SQL Server, you can use the following SQL command:

ALTER TABLE Your_Table_Name ADD RowVersion TIMESTAMP NOT NULL;

The TIMESTAMP data type in SQL Server is a synonym for ROWVERSION. This type of column is automatically updated every time a row is modified.

Upvotes: -1

Adam Henderson
Adam Henderson

Reputation: 308

I'm not too familiar with timestamps in SQL myself but after a quick Google, I would suggest that your understanding of them is wrong. According to

http://www.sqlteam.com/article/timestamps-vs-datetime-data-types

a timestamp does not actually store a date/time. It simply stores a binary number that allows you to identify the order that changes were made to the database.

I'm assuming that is not what you want and that you actually want a date and time? If so, I'd recommend that you use a datetime instead. E.g.: 

create table Items ( id int primary key );

alter table Items 
add ModifiedTime datetime not null 
default current_timestamp;

CREATE TRIGGER itemstrigger
ON Items
AFTER INSERT, UPDATE 
AS 
BEGIN
  UPDATE i
  SET  i.ModifiedTime = current_timestamp
  from inserted ins
  join Items i on i.id = ins.id
END;

SQLFiddle: http://sqlfiddle.com/#!6/ea41c/1

Upvotes: 0

marc_s
marc_s

Reputation: 754468

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

Upvotes: 2

Related Questions