Timestamp conversion

Question

Timestamp conversion

jdbrown239

SSCarpal Tunnel

Points: 4393
More actions
September 13, 2012 at 10:47 am

#264073

In the syssubscriptions table the time stamp is 0x000000000404BAA0. How do I convert that to something i can read like date_time?

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

jdbrown239 (9/13/2012)
In the syssubscriptions table the time stamp is 0x000000000404BAA0. How do I convert that to something i can read like date_time?

the datatype TIMESTAMP is horribly named,and is actually an alias of the ROWVERSION datatype instead.

it is not date or time related in any way...is is nothing more than a binary integer that is unique and changes to a higher value if the data in the row is updated.

if you keep track of the max() value, and then make an update to some rows, any rows that changed will have a value greter than that old max value; that's typically what you can use it for.

you can convert it to a more familiar integer value, but that's about it.

SELECT CONVERT(bigint,0x000000000404BAA0) --67418784

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

The timestamp can also be used to implement optimistic locking. When you read a row of data you also read this column. After the application makes updates, to the row, when it is written back to the database, this column is compared to what is in the database. If the values are the same, the update occurs. If not (something in the row has changed since you read it), the update fails and a message should be returned to the application.

jdbrown239 SSCarpal Tunnel Points: 4393 More actions · Answer 3

OK then my next questions is if I need to recreate the table due to corruption or I ran a repair and lost the most recent data in that column how would I recover or keep the update to the table from failing?