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.

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

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 4

I also wanted to store a history of rowversions to compare with current rowversions to detect changes. To do this, store the rowversion history as varbinary(8):

SELECT OrderID, OrderLineID
, CAST( RowVersion as varbinary(8)) rv -- nullable
FROM SalesOrderLines
INTO SalesOrderLine_hist

reference: https://learn.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql?view=sql-server-ver16

Note that the advice is less clear for SqlServer 2016 and prior (YMMV) https://learn.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql?view=sql-server-2016

Above example is trivial. What I really wanted was the parent's latest child row's rowversion.

This test proves that the cast rv can be compared to the current rv:

select * from (

    select ompSalesOrderID 
    , omlrv.rv
    ,(
        SELECT DISTINCT CAST(MAX(omlRowVersion) as varbinary(8)) as rv 
        FROM SalesOrderLines oml
        WHERE ompSalesOrderID = oml.omlSalesOrderID
                    
    ) as omlRowVersion_last

    from SalesOrders 

    join (
        select distinct 
            omlSalesOrderID
            , MAX(omlRowVersion) rv
        from SalesOrderLines l
        join SalesOrders i on i.ompSalesOrderID=l.omlSalesOrderID
        group by omlSalesOrderID

    ) omlrv
    on ompSalesOrderID=omlrv.omlSalesOrderID

) test 

where rv<>omlRowVersion_last -- returns zero rows
order by ompSalesOrderID