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.)