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.

SSMS doesn't do any such conversion. It displays data in a grid just like *any* other .NET application. Binary values are typically shown as hex or Base64. Why do you want to convert a binary value to a string at all? Why not pass it as is, or use it as a parameter? BTW `rowversion` can't be used as an identifier either, if you thought you can use it as a human-readable key — Panagiotis Kanavos, Dec 07 '16 at 10:32
A [rowversion](https://msdn.microsoft.com/en-us/library/ms182776.aspx) is a `binary(8)` value, just like `bigint`. You can cast it to bigint then use [FORMAT](https://msdn.microsoft.com/query/dev14.query?appId=Dev14IDEF1&l=EN-US&k=k(format_TSQL);k(sql13.swb.tsqlresults.f1);k(sql13.swb.tsqlquery.f1);k(MiscellaneousFilesProject);k(DevLang-TSQL)&rd=true) to get its hex representation, eg `FORMAT(cast(RowVersion)as bigint),"x")` or `FORMAT(cast(cast(RowVersion as binary(8))as bigint),"x")`. Format won't pad the string with zeros though — Panagiotis Kanavos, Dec 07 '16 at 10:44
While it's undocumented, I believe `master.sys.fn_varbintohexstr` will convert any `VARBINARY` type value to an `NVARCHAR` value. See [here](http://www.sqlservercentral.com/Forums/FindPost1708985.aspx) for some info on how this function works, along with source code. — 3N1GM4, Dec 07 '16 at 11:12

Jeroen Mostert · Accepted Answer · 2016-12-07T13:36:19.403

30

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

edited Dec 07 '16 at 13:36

answered Dec 07 '16 at 11:41

Jeroen Mostert

27,176
2
52
85

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. – Neo Dec 07 '16 at 13:31
1

@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. – Jeroen Mostert Dec 07 '16 at 13:37
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 Dec 07 '16 at 13:41
1

@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. – Jeroen Mostert Dec 07 '16 at 13:43
1

@Neo have you tried the edited version of [Jeroen Mostert](http://stackoverflow.com/users/4137916/jeroen-mostert)'s solution? It works for me with the additional step of `CONVERT`ing to `BINARY(8)` first, before then `CONVERT`ing 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. – 3N1GM4 Dec 07 '16 at 13:46
1

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! – Neo Dec 07 '16 at 13:50
@Neo no problem, always happy to admit when someone has a better answer than me! – 3N1GM4 Dec 07 '16 at 14:16

score 3 · Answer 2 · answered Dec 07 '16 at 11:24

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

Thanks. `SELECT TOP 1 'test' + master.sys.fn_varbintohexstr(RowVersion) FROM MyTable` gets me exactly what I need. — Neo, Dec 07 '16 at 13:42

score 2 · Answer 3 · edited Oct 10 '17 at 23:26

2

Have a look at this:

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

edited Oct 10 '17 at 23:26

foxyblue

2,859
2
21
29

answered Oct 10 '17 at 21:20

daniel

21
1

3

Add an explanation to the answer as to why it works. – foxyblue Oct 10 '17 at 21:43

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

3 Answers3

Linked