How do I get date/time information from a TIMESTAMP column?

Question

It seems that the TIMESTAMP information is encrypted in some way, where the date/time data is somehow encoded in binary. I just want to discover all the rows that were modified today.

Answer 1

TIMESTAMP is an unfortunate name the SQL Server team gave the data type. It is for concurrency, and has nothing to do with date or time - they've recommended using its alias, ROWVERSION to prevent confusion. From this Books Online article, "In DDL statements, use rowversion instead of timestamp wherever possible."

Unfortunately you won't be able to derive any date/time details from the ROWVERSION column you already have, but if this information is important, you should add CreatedDate / ModifiedDate columns, for example:

ALTER TABLE dbo.foo ADD CreatedDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE dbo.foo ADD ModifiedDate DATETIME NULL;

Then create a TRIGGER that fires on UPDATE to keep the ModifiedDate value current. You may need to decide whether you want the ModifiedDate to be NULL or equal to CreatedDate on initialization.