RowVersion/Timestamp to string

_{Tuesday, February 12, 2008 3:12 AM}

I have a RowVersion/Timestamp column in my table.  I check against the RowVersion/Timestamp column when updating rows to check if a row has been updated by another user.  I would like to call RAISERROR with a custom message which indicates the current RowVersion/Timestamp of the row in question.

What data type do I convert the RowVersion/Timestamp column into so I can make it part of the custom message which will be a string?

If I convert the RowVersion/Timestamp column into a bigint column, will the max value of RowVersion/Timestamp ever exceed the max value of a bigint? For example, convert(bigint, MyRowVersionColumn)

If the max value of the RowVersion/Timestamp column can exceed the max value for a bigint column, can I convert the RowVersion/Timestamp into a hex string?

Basically RowVersion/Timestamp columns are binary(8) or varbinary(8) so the question could also be answered if it is known if binary(8) can exceed the max value of bigint or if binary(8) can be converted into a hex string

Any help would be appreciated, Thanks!

_{Tuesday, February 12, 2008 3:49 AM ✅Answered}

For those interested, I figured a way out that I think is acceptable:

declare @myrowversion timestamp
declare @myrowbinary binary(8)

select @myrowversion = rowversioncolumn
from mytable

set @myrowbinary = @myrowversion

raiseerror( '%#x', 16, 1, @myrowbinary)

im sure there are other ways...but this way worked for me.

_{Tuesday, February 12, 2008 4:12 AM}

Since the storage for both timestamp and bigint is 8 bytes, you can safely convert from one to the other without hitting the max value, and then convert the result to a string value if you wish.  I believe you can also convert the value to a datetime type as well.

Regards,

Diane.

_{Tuesday, February 12, 2008 7:26 AM}

While converting a timestamp to a datetime datatype may appear to work, the results will be unreliable (and most likely, without meaning) as a datetime value.

(You can also convert any integer or decimal value to a datetime value -within the limited range of valid dates and times.)

What does this provide as a meaningful datetime value?

   SELECT (convert( datetime, 0x00000001 ))

The timestamp datatype is an automatically incrementing sequential binary value and has NOTHING to do with datetime.

_{Tuesday, February 12, 2008 1:47 PM}

You could try an undocumented function in master database, or write your own using same logic.

SELECT master.dbo.fn_varbintohexstr(@@DBTS)

GO

AMB

_{Thursday, February 14, 2008 5:50 PM}

You can use conversion to datetime IF the timestamp value was originally stored from a datetime value in SQL.  Obviously, if the timestamp value is storing values other than dates then you can't use datetime conversion.

Regards,

Diane.

_{Thursday, February 14, 2008 6:04 PM}

Diane,

A column that has its datatype set to RowVersion/Timestamp CANNOT have data inserted into the column. Any attempt to do so will generate the following error:

Server: Msg 273, Level 16, State 1, Line 1
Cannot insert an explicit value into a timestamp column...

A RowVersion/TimeStamp datatype is an auto-generated sequential binary number.

Any conversion to a datetime datatype will produce meaningless data.

_{Thursday, February 14, 2008 9:51 PM}

My mistake.  I'm thinking Oracle.