How to get a date and time from a SQL Server RowVersion value with T-SQL

By: Eric Blinn   |   Updated: 2022-12-02   |   Comments   |   Related: > Dates

Problem

My Microsoft SQL Server database table doesn't have a last updated date/time column, but it has a RowVersion column. Can I turn that column into the date and time that the row was last updated via T-SQL code?

Solution

The short answer is "no."

Nothing in the RowVersion data type is related to the date or time the value was created. It is strictly an 8-byte value that increases sequentially as transactions hit the database. No "conversion" or function call can change that value into a date or time.

The long answer is much more complicated and will be the subject of this tip.

While Microsoft SQL Server does not associate this 8-byte value with the date or time it was created, you certainly can. To do that, we'll first need to learn a little bit about RowVersion and how it works.

How RowVersion Works

At its core, RowVersion is just a number. It is stored as 8 bytes or 64 bits and presented to the users as its hexadecimal value. This is a RowVersion value, as shown in SSMS query output.

This is far easier to read than if it were presented as an equally large numeric value where the 64 bits could reach 18,446,744,073,709,551,616 in decimal form.

As transactions hit the database, the values assigned to RowVersion columns increase sequentially. For this demo, a new table was created in a database with no activity beyond this process, and four rows were created via this T-SQL code:

CREATE TABLE RowVersionTest(
  i INT IDENTITY
, RV RowVersion
, SomeText CHAR(1) DEFAULT ('A'));
GO

INSERT INTO RowVersionTest DEFAULT VALUES;
INSERT INTO RowVersionTest DEFAULT VALUES;
INSERT INTO RowVersionTest DEFAULT VALUES;
INSERT INTO RowVersionTest DEFAULT VALUES;

SELECT i, RV FROM RowVersionTest;

The query output shows that each row received a different RowVersion value that was exactly one larger than the row before. Notice that these values are not times, nor are they a number of seconds or microseconds since some start time. They are just numbers represented in hexadecimal.

Each SQL Server database gets its own independent incrementing number. Each table within a single database will share that one counter. The most recently assigned RowVersion value for a database can be viewed by querying the value @@DBTS. This query will show the most recently assigned RowVersion values for two different databases.

USE WideWorldImporters;
GO
SELECT @@DBTS  WWI_DBTS;
GO
USE master;
GO
SELECT @@DBTS master_DBTS;

Despite these two databases being on the same instance of SQL Server, they are not on the same RowVersion sequence.

Finally, no two rows can have the same RowVersion value. This T-SQL query will update all four rows from the prior demo in a single statement.

SELECT * FROM RowVersionTest;
UPDATE RowVersionTest Set SomeText = 'B';
SELECT * FROM RowVersionTest;

Despite the four rows being updated in one transaction, they received four unique RowVersion values.

Associating SQL Server RowVersion Values to Dates and Times

Having seen how RowVersion values are assigned, it is unreasonable to think that every single RowVersion value can be associated with its own date and time. If that level of detail is needed, then a datetime2 column needs to be added to the table(s) in question and updated whenever the row is inserted or updated. See this tip for assistance: Mimic timestamp behavior of other database platforms to store last modified date.

If an approximation of the time associated with a RowVersion value is satisfactory, then this can be handled with a custom table and an accompanying function. Since @@DBTS will return the last RowVersion assigned to any row and SQL Server knows what time it is now, a table can be made to store the two values. This table can be populated with a new row on a schedule. Later, when a RowVersion value is found in a table and sent to the function, the function will search this table for the nearest RowVersion value and report back that date and time.

When building the custom table, one question needs to be answered: how precise does the table need to be? The answer to this question will determine the data type for the date/time column. A smalldatetime value uses only 4 bytes and has precision down to the minute. It can store dates up to June 6, 2079. A datetime2(3) uses 6 bytes and has accuracy to the 100^th of a second. It can keep dates up to the year 9999.

Consider how often a row will be added to that table and choose the date/time data type that makes the most sense. For instance, if a row will be added every 5 minutes, then smalldatetime would be the better choice. If a row is added every 30 seconds, then datetime2 should be used.

This plan should be reconsidered if the accuracy needed is significantly higher than 30 seconds.

Create the Custom Table

The custom table needs only two columns: the RowVersion value and the date and time. There are two ways to retrieve the RowVersion value. Both are shown here with comments to help choose which makes more sense for a given scenario. Both show options to show a SMALLDATETIME for the date and time, meaning it will have a precision no higher than 1 minute.  Here is the T-SQL code:

--Option 1.
--Must be careful to NEVER update any row
--as this would change the RV column value.
CREATE TABLE dbo.RowVersionTimeEstimates(
  RV ROWVERSION
, WhenWasIt SMALLDATETIME NOT NULL DEFAULT(SYSDATETIME()));
 
--Option 2.
--If a database has zero activity for a period then the insert 
--could fail due to a primary key violation.
CREATE TABLE dbo.RowVersionTimeEstimates(
  RV BINARY(8) NOT NULL DEFAULT (@@DBTS)
, WhenWasIt SMALLDATETIME NOT NULL DEFAULT(SYSDATETIME()));
 
--Either way, we need that clustered PK and probably want to use compression.
ALTER TABLE dbo.RowVersionTimeEstimates ADD CONSTRAINT
   PK_RowVersionTimeEstimates PRIMARY KEY CLUSTERED (RV) 
   WITH(DATA_COMPRESSION=PAGE);

Populate the Custom Table

A SQL Server Agent job can add a new row to this table every few minutes or seconds as needed. The statement is straightforward.

INSERT INTO dbo.RowVersionTimeEstimates DEFAULT VALUES;

For this demo, the job will be running every 5 minutes.

Create a T-SQL Function to Convert SQL Server RowVersion Values to Associated Dates and Times

This function will convert a RowVersion value to a date and time near when it was generated. While this version looks for the end of the possible range, it could be easily modified to choose the beginning of the time frame. It could also return both times and offer a window of time during which the action was taken.

CREATE OR ALTER FUNCTION dbo.ConvertRowVersionToDateTime (@DBTS BINARY(8))
RETURNS SMALLDATETIME
AS
BEGIN
  DECLARE @RetVal SMALLDATETIME;
  SELECT TOP 1 @RetVal = WhenWasIt FROM dbo.RowVersionTimeEstimates WHERE RV >= @DBTS;
  --If we have yet to record a value this big, that indicates the value is extremely new.
  IF @@ROWCOUNT = 0 SET @RetVal = SYSDATETIME();
  RETURN @RetVal;
END;

Final Thoughts

It is important to remember that if this table is added to a database long after it has been in use, it will only be able to calculate approximate date and time values from rows created or modified after it was put into practice.

Next Steps

• Synchronize SQL Server data using RowVersion
• SQL Server Timestamp vs RowVersion
• Mimic the timestamp feature from other RDBMS using a trigger

About the author

Eric Blinn is the Sr. Data Architect for Squire Patton Boggs. He is also a SQL author and PASS Local Group leader.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips