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?

Solution 1

A timestamp datatype is managed by SQL Server. I've never seen it used anywhere other than as a table column type. In that capacity, the column of type timestamp will give you a rigorous ordinal of the last insert/update on the row in relation to all other updates in the database. To see the most recent ordinal across the entire database, you can retrieve the value of @@DBTS or rowversion().

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

timestamp (Transact-SQL)

is a data type that exposes automatically generated, unique binary numbers within a database. timestamp is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The timestamp data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime data type.

Hence, the volatile value of a timestamp column cannot be set and is subject to change upon any modifaction to the row. You can, however, freeze the timestamp value to a varbinary(8) value.

For example, say you had 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, in an extraction process, you might want to capture everything that has been updated since the last time you ran 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 are having issues, my first guess would be that crux of your problem is in the conversion of a varchar to a varbinary(8), and not to a timestamp type.

For more info (perhaps too much) , see the comment (fourth one down) I left to the blog post http://vadivel.blogspot.com/2004/10/about-timestamp-datatype-of-sql-server.html?showComment=1213612020000

Solution 2

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

Solution 3

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.

Comments

[scoopr]

Please see updated Question. Sorry it wasn't clear before. Imagine getting a parameter passed in as VARCHAR (but it has a TIMESTAMP value inside it). How would you make @myParam then?