Reading timestamp type

A select of a timestamp field just returns <binary>. How do I have a look at the values in human-readable form?


Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

Scott PletcherConnect With a Mentor Senior DBACommented:
Since it's a counter, you can identity all the rows that have changed since a given time by finding all values of the counter large than that value.  This is most useful for replication.

For example, say the db is snap-shotted (copied) at 11:00am; the current "timestamp" counter is 0x50.  At 1:00pm SQL wants to copy all *added/modified* rows to the other db.  It simply looks for "timestamps" greater than 0x50 and can ignore all other rows.
Scott PletcherSenior DBACommented:
A "timestamp" column does not actually contain a date-time.  In fact it has *no* relation to an actual date-time; it is more a binary counter of changes.  Thus, there is no way to convert it to its corresponding datetime.
Hi RichardFox,

There is no human-readable form of the timestamp field. This simply contains an 8-digit binary value that is guaranteed to be unique within a database, similar to a GUID but not as unique. It doesn't really have anything to do with time or date values. (Well that is the Microsoft version anyway, the SQL-92 standard does have a timestamp definition but this is represented in SQL server by the DateTime datatype.)

Tim Cottee
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Brian CroweDatabase AdministratorCommented:
You're welcome to cast it to a bigint if you want but it won't provide you any more info than the binary version does.
RichardFoxAuthor Commented:
OK thanks. I really fail to see the usefulness of such a data type, a timestamp that is not really a timestamp! If you need a unique counter, you can use a row id. What is this type's purpose?
Timestamp is not "unreadable". EM doesn't display any binary columns. Run you query in isqlw.exe, and you will see them.
All Courses

From novice to tech pro — start learning today.