1

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

Improve this question
3
  • What's the "on update current_timestamp" supposed to mean? Commented Nov 11, 2015 at 13:58
  • Accourding to sources from google :) When someone use update a row in the table "Items" then this ModifiedTime column will automatically update with the latest "current_timestamp" using this "on update" part. I have another column called CreatedTime, and that one works fine for its purpose which is to register the datetime of which the row was created in the table Items. But then i wanted another column "ModifiedTime" which tells me the time when someone "UPDATED" this row for example, changed a value in the row. Commented Nov 11, 2015 at 14:01
  • Recommended reading: Create trigger for auto update modified date with SQL Server Commented Nov 11, 2015 at 14:13

3 Answers 3

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Okay! So how would i create this column? What query would i need to enter? And to keep this column updated when the row updates, i need a trigger you say, any tips on what this would be? The CreatedTime column i already have as meantioned above, was created using this query: Alter table Items add CreatedTime datetime not null default getdate(); do i need to make any changes to that column or is that one ok?
@Mikael: if you're on SQL Server 2008 or newer, I'd recommend to stop using DATETIME and instead use DATETIME2(3) (or any other value for the precision, from 0-7 - 3 for milliseconds is a sensible default)
Yes im on SQL server 2014, so CreatedTime should also use datetime2(3) then
Yes, definitely - DATETIME has some oddball restrictions - accuracy of only 3.33ms, date range from 1/1/1753 to 12/31/9999 etc. - DATETIME2(3) does away with all those restrictions
0

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

Improve this answer

Comments

-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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.