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.

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

Solution 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  |
\--------------------------------------------------------------------/

Solution 3

Have a look at this:

SELECT 
substring(replace(replace(replace(replace(cast(CAST(GETDATE() AS datetime2) as 
varchar(50)),'-',''),' ',''),':',''),'.',''),1,18)

Comments

[Neo]

Have you tried this? It returns Explicit conversion from data type timestamp to nvarchar(max) is not allowed. If I change it to VARCHAR, there's no error, but it still shows garbage as described in the Question.

[Jeroen Mostert]

@Neo: My bad, I incorrectly assumed SQL Server would treat ROWVERSION as a binary type. It does not, so an intermediate conversion step is necessary to make that work.

[Neo]

The accepted Answer is the best solution I think. SELECT TOP 1 'test' + master.sys.fn_varbintohexstr(RowVersion) FROM MyTable gets me exactly what I need.

[Neo]

Thanks. SELECT TOP 1 'test' + master.sys.fn_varbintohexstr(RowVersion) FROM MyTable gets me exactly what I need.

[Jeroen Mostert]

@Neo: relying on undocumented functions is demonstrably worse than relying on the built-in functionality of CONVERT. What you accept is up to you, of course, but I know what I'd prefer in a production environment.

[3N1GM4]

@Neo have you tried the edited version of Jeroen Mostert's solution? It works for me with the additional step of CONVERTing to BINARY(8) first, before then CONVERTing to NVARCHAR... As such, and for the reasons given above by Jeroen, I have upvoted this answer as I believe it's better than my own.

[Neo]

OK, my bad this time. Yes, the edited version works. In fact, I now prefer this answer as the hex letters remain upper case (looks nicer). It's not production code (it's just a script to help me in dev), but I prefer doing things the most proper way, and I think this is it. I'll change the accepted Answer to this one, but still upvote your one, @3N1GM4. Thanks for the honesty!

[3N1GM4]

@Neo no problem, always happy to admit when someone has a better answer than me!

[foxyblue]

Add an explanation to the answer as to why it works.