In T-SQL, a rowversion is a binary data type that is automatically generated and assigned a new value every time a row is inserted or updated in a table. It is also known as timestamp in earlier versions of SQL Server.

The rowversion data type is primarily used for versioning and tracking changes in tables. You can use it to determine the last time a row was updated or to identify changes to a row.

To get the time from a rowversion value in T-SQL, you can convert it to a datetime data type using the CONVERT function.

Here’s an example of how to get the time from a rowversion:

 

DECLARE @rv rowversion = 0x00000000000123AB;

SELECT CONVERT(DATETIME, @rv) AS TimeFromRowversion;

 

In this example, @rv is a rowversion value represented as a hexadecimal number. The CONVERT function is used to convert the rowversion value to a DATETIME value, which represents the time when the row was last updated.

The result of this query will be a DATETIME value that represents the time when the row was last updated.

It’s important to note that the rowversion value does not store the actual date and time of the row update. Instead, it stores a unique binary value that changes every time the row is updated.

Therefore, the time obtained from a rowversion value is the time when the row was last updated, not the actual time of the update.

In addition to using the CONVERT function, you can also use the DATEADD function to add the rowversion value to a base date to get the actual time of the update. For example:

 

DECLARE @rv rowversion = 0x00000000000123AB;

DECLARE @baseDate DATETIME = '1970-01-01';

SELECT DATEADD(SECOND, CAST(@rv AS BIGINT), @baseDate) AS ActualUpdateTime;

 

In this example, the DATEADD function is used to add the rowversion value, which is cast to a BIGINT, to the @baseDate value. This gives you the actual time of the row update in DATETIME format.

Conclusion 

To get the time from a rowversion in T-SQL, you can convert it to a DATETIME data type using the CONVERT function, or use the DATEADD function to add it to a base date to get the actual time of the update.