How to convert TIMESTAMP values to VARCHAR in T-SQL as SSMS does?

Question

I am trying to convert a TIMESTAMP field in a table to a string so that it can be printed or executed as part of dynamic SQL. SSMS is able to do it, so there must be a built-in method to do it. However, I can't get it to work using T-SQL.

The following correctly displays a table result:

SELECT TOP 1 RowVersion FROM MyTable

It shows 0x00000000288D17AE. However, I need the result to be part of a larger string.

DECLARE @res VARCHAR(MAX) = (SELECT TOP 1 'test' + CONVERT(BINARY(8), RowVersion) FROM MyTable)
PRINT(@res)

This yields an error: The data types varchar and binary are incompatible in the add operator

DECLARE @res VARCHAR(MAX) = (SELECT TOP 1 'test' + CONVERT(VARCHAR(MAX), RowVersion) FROM MyTable)
PRINT(@res)

This results in garbage characters: test (®

In fact, the spaces are just null characters and terminate the string for the purpose of running dynamic SQL using EXEC().

DECLARE @sql VARCHAR(MAX) = 'SELECT TOP 1 ''test'' + CONVERT(VARCHAR(MAX), RowVersion) FROM MyTable'
EXEC (@sql)

This just displays a table result with the word "test". Everything after "test" in the dynamic SQL is cut off because the CONVERT function returns terminating null characters first.

Obviously, what I want the resultant string to be is "test0x00000000288D17AE" or even the decimal equivalent, which in this case would be "test680335278".

Any ideas would be greatly appreciated.

Answer 1

SELECT 'test' + CONVERT(NVARCHAR(MAX), CONVERT(BINARY(8), RowVersion), 1). The trick is the 1 to the CONVERT as the style, per the documentation. (Pass 2 to omit the 0x.)

Answer 2

As mentioned in the comments, the undocumented function master.sys.fn_varbintohexstr will convert binary to string such that you could then concatenate with some other string value:

DECLARE @binary BINARY(8)
SELECT @binary = CAST(1234567890 AS BINARY(8))

SELECT @binary AS BinaryValue, 
       LEFT(master.sys.fn_varbintohexstr(@binary),2) + UPPER(RIGHT(master.sys.fn_varbintohexstr(@binary),LEN(master.sys.fn_varbintohexstr(@binary))-2)) AS VarcharValue,
       'test' + LEFT(master.sys.fn_varbintohexstr(@binary),2) + UPPER(RIGHT(master.sys.fn_varbintohexstr(@binary),LEN(master.sys.fn_varbintohexstr(@binary))-2)) AS ConcatenatedVarcharValue

I went ahead and split the first two characters and did not apply the UPPER function to them, to exactly reproduce the format as displayed when a binary value.

Results:

/--------------------------------------------------------------------\
|     BinaryValue    |    VarcharValue    | ConcatenatedVarcharValue |
|--------------------+--------------------+--------------------------|
| 0x00000000499602D2 | 0x00000000499602D2 |  test0x00000000499602D2  |
\--------------------------------------------------------------------/