How to convert VARCHAR to TIMESTAMP in MSSQL?

Question

You want to call a stored proc in MS SQL that has the TIMESTAMP parameter type in T-SQL, not ADO.NET, using the VARCHAR value (for example, "0x0000000002C490C8").

What do you do?

UPDATE: Here you have the value "Timestamp", which appears in you, but exists only as VARCHAR. (Think of the OUTPUT variable in another stored proc, but it is already fixed as VARCHAR, it just has a TIMESTAMP value). So, if you have not decided to create Dynamic SQL, how to programmatically change the value stored in VARCHAR to a valid TIMESTAMP?

Answer 1

The timestamp data type is managed by SQL Server. I have never seen it used everywhere except as a table column type. As such, a column of type timestamp will give you the strict serial number of the last insert / update in the row with respect to all other updates in the database. To see the last serial number of the entire database, you can get the value @@ DBTS or rowversion ().

Per http://msdn.microsoft.com/en-us/library/ms182776(SQL.90).aspx

timestamp (Transact-SQL)

is a data type that provides automatically generated unique binary numbers in a database. A timestamp is commonly used as a mechanism for stamping table rows. Storage size is 8 bytes. The timestamp data type is just an increasing number and does not save the date or time. To write a date or time, use the datetime data type.

Therefore, the volatility value of the timestamp column cannot be set and can be changed with any modification to the row. However, you can freeze the timestamp value for the varbinary (8) value.

For example, let's say you have a source table and a target table.

CREATE TABLE tblSource (
Id int not null
colData int not null
colTimestamp timestamp null)

CREATE TABLE tblTarget (
Id int not null
colData int not null
colTimestampVarBinary varbinary (8) null)

Then, during the extraction process, you can capture everything that has been updated since the last launch of the extraction process.

DECLARE @maxFrozenTargetTimestamp varchar(8)
SELECT @maxFrozenTargetTimestamp = max(colStamp) FROM tblTarget

INSERT tblTarget(Id, colData, colTimestampVarBinary)
SELECT
Id
,colData
, colTimestampVarBinary = convert(varbinary(8) colTimestamp)
FROM
tblSource
WHERE
tblSource.colTimestamp > @maxFrozenTargetTimestamp

If you were having problems, my first guess would be that the problem of your problem is in converting varchar to varbinary (8), and not to label type.

For more information (maybe too much), see the commentary (fourth shot). I left a blog post http://vadivel.blogspot.com/2004/10/about-timestamp-datatype-of-sql-server.html?showComment=1213612020000

Answer 2

Since the timestamp is compatible with varbinary, the solution will be in SQL Server 2008:

 declare @hexstring varchar(max); set @hexstring = '0xabcedf012439'; select CONVERT(varbinary(max), @hexstring, 1); set @hexstring = 'abcedf012439'; select CONVERT(varbinary(max), @hexstring, 2); 

Directory. MSN Blogs

Answer 3

TIMESTAMP is semantically equivalent to VARBINARY (8) (nullable) or BINARY (8) (non-nullable). Thus, you should be able to call the procedure with the parameter without quotes, as follows:

 EXEC usp_MyProc @myParam=0x0000000002C490C8 

See also Electronic Documentation by Email

EDIT for updated question ...

I just tried a few experiments. Honestly, I'm curious how you got this as a varchar in the first place, since when I do something like:

 select top 10 convert(varchar, ts) from foo 

Where ts is the timestamp, I get 10 empty lines. (If I do not convert, I see my timestamps.)

However, I tried to work with him from the right direction ... I did this:

 select convert(timestamp, '0x0000000000170B2E') 

And the conversion led to 0x3078303030303030 . So it will not be either. Also will not be converted to binary.

I don't like to say it, but you can get stuck in the dynamic world of SQL. I would really like to be wrong.