How to convert TIMESTAMP values to VARCHAR in T-SQL as SSMS does?
<p>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.</p>

<p>The following correctly displays a table result:</p>

<pre><code>SELECT TOP 1 RowVersion FROM MyTable
</code></pre>

<p>It shows <code>0x00000000288D17AE</code>. However, I need the result to be part of a larger string.</p>

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

<p>This yields an error: <code>The data types varchar and binary are incompatible in the add operator</code></p>

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

<p>This results in garbage characters: <code>test    (®</code></p>

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

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

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

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

<p>Any ideas would be greatly appreciated.</p>