Is there a way to get DateTime value from timestamp type column?

Question

I need a select from table which does not have column that tells when row was inserted, only timestamp column (values like: 0x0000000000530278). Some data was imported to the table yesterday and now I need to find out what exactly was imported :(

Is there a way to do it using only timestamp info? Here I found that:

• Timestamp is a 8 bytes sequential Hex number, that has nothing to do with neither the date nor the time.
• To get the current value of timestamp, use: @@DBTS.

Perhaps there is a way to find what was timestamp value around specific time? That would help to form a select. Or maybe there is a well known solution?

Answer 1

The Transact-SQL timestamp data type is a binary data type with no time-related values.

So to answer your question: Is there a way to get DateTime value from timestamp type column?

The answer is: No

Answer 2

The timestamp datatype in SQL Server 2005 is a synonym of rowversion and is just a number that is automatically incremented with each row update.

You can cast it to bigint to see its value.

To get what you want for new or updated rows, you should propably add another datetime column (lastupdate) and a trigger to update that column with each update.

For rows that have already been inserted in the past I don't think that you can do something to find the exact time.