像SSMS一样，如何在T-SQL中将TIMESTAMP值转换为VARCHAR? [英] How to convert TIMESTAMP values to VARCHAR in T-SQL as SSMS does?

问题描述

我正在尝试将表中的TIMESTAMP字段转换为字符串，以便可以将其作为动态SQL的一部分进行打印或执行. SSMS能够执行此操作，因此必须有一个内置方法来执行此操作.但是，我无法使其使用T-SQL.

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

它显示0x00000000288D17AE.但是，我需要将结果作为较大字符串的一部分.

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)

这会产生错误: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)

这会导致垃圾字符:test (®

实际上，空格只是空字符，并终止字符串，目的是使用EXEC()运行动态SQL.

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)

这仅显示带有单词"test"的表格结果.由于CONVERT函数首先返回终止的空字符，因此动态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.

很明显，我希望结果字符串为"test0x00000000288D17AE"或什至是十进制等效值，在这种情况下为"test680335278".

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.

推荐答案

SELECT 'test' + CONVERT(NVARCHAR(MAX), CONVERT(BINARY(8), RowVersion), 1).诀窍是根据文档，将1用作CONVERT的样式. (通过2忽略0x.)

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

这篇关于像SSMS一样，如何在T-SQL中将TIMESTAMP值转换为VARCHAR?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！