How do you convert VARCHAR to TIMESTAMP in MSSQL?

Question

You'd like to call a stored proc on MS SQL that has a parameter type of TIMESTAMP within T-SQL, not ADO.NET using a VARCHAR value (e.g. '0x0000000002C490C8').

What do you do?

UPDATE: This is where you have a "Timestamp" value coming at you but exists only as VARCHAR. (Think OUTPUT variable on another stored proc, but it's fixed already as VARCHAR, it just has the value of a TIMESTAMP). So, unless you decide to build Dynamic SQL, how can you programmatically change a value stored in VARCHAR into a valid TIMESTAMP?

Answer 1

Since timestamp is compatible with varbinary the solution will be this 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);

Reference. MSN Blogs

Answer 2

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

EXEC usp_MyProc @myParam=0x0000000002C490C8

See also SQL Books Online

EDIT for updated question ...

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

select top 10 convert(varchar, ts) from foo

Where ts is a timestamp, I get 10 blank rows. (If I don't convert, I see my timestamps.)

However, I tried working at it from the proper direction ... I did this:

select convert(timestamp, '0x0000000000170B2E')

And the conversion resulted in 0x3078303030303030. So that won't play either. Nor will converting to binary.

I hate to say it, but you might be stuck in a dynamic SQL world. I'd really like to be wrong, though.