SQL Server Timestamp using with C#

Question

In my database I have used Timestamp in each table to see when data was inserted.

It stores data in byte[] of 8 byte.

Now I want to read that time using C#.

How can I get DateTime object from Timestamp which is byte[]?

Answer 1

SQL Server's TIMESTAMP datatype has nothing to do with a date and time!

It's just a binary representation of a consecutive number - it's only good for making sure a row hasn't change since it's been read.

In never versions of SQL Server, it's being called RowVersion - since that's really what it is. See the MSDN docs on ROWVERSION:

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

So you cannot convert a SQL Server TIMESTAMP to a date/time - it's just not a date/time.

But if you're saying timestamp but really you mean a DATETIME column - then you can use any of those valid date formats described in the CAST and CONVERT topic in the MSDN help. Those are defined and supported "out of the box" by SQL Server. Anything else is not supported, e.g. you have to do a lot of manual casting and concatenating (not recommended).

The format you're looking for looks a bit like the ODBC canonical (style = 121):

DECLARE @today DATETIME = SYSDATETIME()

SELECT CONVERT(VARCHAR(50), @today, 121)

gives:

2011-11-14 10:29:00.470

SQL Server 2012 will finally have a FORMAT function to do custom formatting......