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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mikael
  • 35
  • 1
  • 7
  • What's the "on update current_timestamp" supposed to mean? – jarlh Nov 11 '15 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. – Mikael Nov 11 '15 at 14:01
  • Recommended reading: [Create trigger for auto update modified date with SQL Server](http://stackoverflow.com/a/7737993/1225845) – AHiggins Nov 11 '15 at 14:13

2 Answers2

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 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 Nov 11 '15 at 14:13
  • @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) – marc_s Nov 11 '15 at 14:17
  • Yes im on SQL server 2014, so CreatedTime should also use datetime2(3) then – Mikael Nov 11 '15 at 14:18
  • 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 – marc_s Nov 11 '15 at 14:21
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

Adam Henderson
  • 309
  • 3
  • 12