Timestamp field confusion

[jemckinney]

I sure hope someone can shed some light on this for me. I have a very large database and each table has a timestamp field. But when I query the table and pull out this information it appears as 0x0000000000000 (zeros may be alphanumeric). I have done a lot of reading on this field and datatypes. I looked at the properties of this field and it is set as timestamp datatype 8 char, not null. I need to be able to use this field to determine when records were actually created but cannot seem to find how to convert this to a readable format or if I could understand how this field is stored.

I appreciate any help or guidance you can give.

TIA!

 

[bborissov]

Timestamp field is not realy related to date or the time.
From BOL:

The SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamps are binary numbers that indicate the relative sequence in which data modifications took place in a database. The timestamp data type was originally implemented to support the SQL Server recovery algorithms.

It further states

Never use timestamp columns in keys, especially primary keys, because the timestamp value changes every time the row is modified.

You could add a DateTime field and put its default value to GetDate() AND never touch that field in your frontend.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[jemckinney]

Thank you for your reply. This is a database created from another company's software and I have been tasked with pulling info out of it. The table has a dateadded field and a last modified field along side the ts field. But VP wants me to figure out from the timestamp (which we were guessing was when the record was created) minus 7 days to find out if someone tampered with the actual dateadded fields. I guess I am unable to do this if the ts field actually has nothing to do with when the record was created.

 

[SQLSister]

That would be correct. Timestamp changes every time the data is changed in any way. It has no conversion to date-time. It is mostly used to keep systems in synch. I use it for instance to determine which records have been changed since the last feed I sent to one of our clients.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief