How to convert timestamp / rowversion to a date and time in SQL Server?

Question

How to convert timestamp / rowversion to a date and time in SQL Server?

I have several tables where I would like to track the last update, with column names like ModifiedAt, UpdatedAt, UpdatedDate.

Would this be possible if these were with a Rowversion data type or Timestamp data type?

as in

CREATE TABLE dbo.Customer (
    CustomerID BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    CustomerName NVARCHAR(200) NOT NULL ,
    CreatedAt DATETIME2 NOT NULL CONSTRAINT df_Customer_CreatedAt DEFAULT GETUTCDATE(),
    UpdatedAt ROWVERSION NOT NULL /* does this work ? */
) 

Answer 1

It is not possible to convert the TimeStamp colunm into a Date & Time format. Microsoft has renamed the TimeStamp data type to RowVersion to avoid the confusion.

The timestamp/rowversion data type is really just a Database Transaction ID, and it can also be stored in a column of the Binary(8) data type.

But here is a work-around to implement the ModifiedAt, UpdatedDate column type. This will translate (or "convert") a RowVersion column into a SmallDateTime datatype, that can be formatted according to your needs.

But you need to do a bit of work;

1. Create a table "UpdateTimeStamp" with three columns

(CreatedDate Smalldatetime, NewRowVersion, OldRowVersion) like this:

Create Table dbo.UpdateTimeStamp(
    OldRowVersion binary(8) not null, 
    CreatedDate SmallDateTime not null constraint DF_UpdateTimeStamp_CreatedDate Default getdate(),
    NewRowVersion ROWVERSION not null CONSTRAINT PKUpdateTimeStamp PRIMARY KEY CLUSTERED
)

Insert the first row manually

Insert into dbo.UpdateTimeStamp(OldRowVersion, CreatedDate) 
VALUES (0x0000000000000000, '2000-01-01')

2. Create a SQL Agent job

Create a standard SQL Agent job which inserts one row in the table every one minute.

Make step 1 run this code:

Insert into dbo.UpdateTimeStamp(OldRowVersion) 
SELECT TOP (1) NewRowVersion 
FROM dbo.UpdateTimeStamp 
ORDER BY NewRowVersion DESC

Set the schedule to run every 1 minute.

3. Query the data

You need to write your query with a join to the UpdateTimeStamp table to your table with a between join clause like this:

SELECT top 10000 mt.*, uts.CreatedDate AS ModifiedDate 
FROM dim.MyTable MT 
LEFT JOIN dbo.UpdateTimeStamp uts 
    ON MT.DT1RowVersion > OldRowVersion AND MT.DT1RowVersion <= NewRowVersion
ORDER BY uts.CreatedDate

Caveat, buts...

1) Microsoft SQL Server is not very good at "date range join" clauses, so if you do not need minute resolution, but 10 minute accuracy is acceptable, then you can improve the query speed a bit with lowering the frequency of the SQL Agent job to 10 minute intervals.

2) This will only work for time periods where the SQL Agent job has been running, as it is not easy to create the dbo.UpdateTimeStamp table retrospectively. Unless you have an old table with a RowVersion/TimeStamp datatype, and this table also never sees updates, and the table has a CreatedDate column. And the table has to be in the same database on the same server.

Bonus features

The RowVersion column was already added to many tables already in our Data Warehouse, because I use it as a watermark for loading data into our Data Marts. So now I have the this feature as a debug option, when I need to investigate when things went wrong in our facts and dimensions. Furthermore I do not need a trigger, which may have performance issues and/or stability issues, if not coded correctly.