I'm trying to add a new column to an existing SQL Server table with the data type of TIMESTAMP.
This is my script:
ALTER TABLE OrderDetails
ADD ModifiedTime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
It should be not null. Running this script results in an error
Defaults cannot be created on columns of data type timestamp
I tried executing it without DEFAULT CURRENT_TIMESTAMP. But then it says
Cannot insert the value NULL into column 'ModifiedTime'
Any advises please?
marc_s :
TIMESTAMP in SQL Server has absolutely nothing to do with date & time (thanks to Sybase for screwing that one up!) - it's just a system-internal, binary counter (often used for opmistic concurrency checks). It's been deprecated, too, and renamed ROWVERSION which is much clearer as to what it is.\nFor date & time - use DATE (if you need only date - no time), or DATETIME2(n) datatypes:\nALTER TABLE OrderDetails \n ADD ModifiedTime DATETIME2(3) NOT NULL DEFAULT CURRENT_TIMESTAMP\n\nSee the official Microsoft docs for more details on date&time datatypes and functions",
2020-10-06T04:55:09