How do I get date/time information from a TIMESTAM

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

Depending on usage scenario and the scale of precision that you need you can use following technic: As far as TIMESTAMP is something like global counter you can add one global table with 2 columns:

datetime,timestamp

and make some JOB insert values there every N minutes (depending on required precision). Job will insert NOW() into datetime column and current TIMESTAMP value. In this way you get some kind of "time ruler" and you always can determine which timespan your particular TIMESTAMP from another table belongs to. Sample: You have timestamp value 0x000121 and look for timespan, when it was generated. Your table has values

20120501 12:00:00   0x000001
20120501 12:15:00   0x000061
20120501 12:30:00   0x000091
20120501 12:45:00   0x000151

Using select query you will be able to determine, that 0x000121 lies between 20120501 12:30:00 and 20120501 12:45:00

If you have no possibility to create such table/job you can look into database and determine other tables with timestamp and maybe you will be lucky and will find datetime column there as well (filled with NOW()), then you can use that table as "time ruler".

Answer 2

TIMESTAMP is just an incremental, per-row value. It does not hold any actual date/time information.

What you need is for example an actual DATETIME column with its default value set to GETUTCDATE() or something like that.

Answer 3

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.