How to decode timestamp

Question

How to decode timestamp

Feebie

Right there with Babe

Points: 785
More actions
July 8, 2009 at 2:57 pm

#93771

Hi,
i need to help to decode the timestamp values which are there on my tables.
For example i have a timestamp value like '0x00000000085EBC62'.
I would like to see the value in date and time format.
Please let me know.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Anjana (7/8/2009)
Hi,
i need to help to decode the timestamp values which are there on my tables.
For example i have a timestamp value like '0x00000000085EBC62'.
I would like to see the value in date and time format.
Please let me know.

the timestamp data type is not actually a date time value.

From BOL (Books Online):

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.

Feebie Right there with Babe Points: 785 More actions · Answer 2

So using Timestamp could i keep track of recent updates on the table row values?

Feebie Right there with Babe Points: 785 More actions · Answer 3

So using Timestamp could i keep track of recent updates on the table row values?

Carl Federl One Orange Chip Points: 25384 More actions · Answer 4

So using Timestamp could i keep track of recent updates on the table row values?

No and here is a scenario: Versus two different SQL Servers get the latest value for the rowversion by running "SELECT @@DBTS". Now imagine that the SQL Server that has the lower value will replace the other SQL Server and the user database is migrated using backup/restore.

Then on the new server, when a table row is updated, the value of the rowversion column will have a lower value than other rows that were updated on the old SQL Server.

Rowversion can be used to prevent overlapping updates but requires the capturing of the rowversion column in selects, tracking of the rowversion by

the application, and including the tracked rowversion in the where clause of all update and delete statements.

SQL = Scarcely Qualifies as a Language

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 5

Carl is correct about tracking changes from Server to Server and database to database. However, if you wanted to track changes within a given database you could, but you would have to create a mechanisim to capture the current/greatest TimeStamp in order to tell what has changed.

Timestamp/Rowversion has it's place, but if you have the option I'd suggest using some else like a Datetime that has more intrinsic meaning.