CODEWITHSUNDEEP
sqlsql-server
Kevin
Kevin

Reputation: 434

Unable to add a new column to SQL Server table with TIMESTAMP data type

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?

Upvotes: 2

Views: 3335

Answers (2)

marc_s
marc_s

Reputation: 754598

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.

For date & time - use DATE (if you need only date - no time), or DATETIME2(n) datatypes:

ALTER TABLE OrderDetails 
    ADD ModifiedTime DATETIME2(3) NOT NULL DEFAULT CURRENT_TIMESTAMP

See the official Microsoft docs for more details on date&time datatypes and functions

Upvotes: 12

AhmerMH
AhmerMH

Reputation: 688

Try the following solution provided here: https://www.sql-server-performance.com/defaults-not-created-timestamp/

ALTER TABLE OrderDetails ADD ModifiedTime TIMESTAMP NOT NULL DEFAULT 0

Upvotes: 0

Related Questions