How to view the timestamp value of fields defined as binary(8)

I have a table with a few columns which are defined as binary(8)

MinTimeStamp(binary(8) null)
MaxTimeStamp(binary(8) null)

Is there any way I can convert this to a timestamp value that is readable

I tried convert(timestamp,MinTimeStamp) it returns something like this 0x0000000110E787D9.
Is it possible to convert it to something like Feb 07, 2008 12:10:20:345
or 2008-02-07 12:10:20:345
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Timestamp type has nothing to do with date and time. It is just a binary value. Timestamp column added to a table indicates if the record was updated or not. It is always set to new value on any insert or update. Any new value is grater than all past values in the database and is guaranted to be unique within database. Only comparing timestamp value to one saved previously makes sense.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
countrymeisterAuthor Commented:

So a column defined as binary(8) though named timestamp stores a unique binary value
No. Timestamp is a datatype not a special column name. But you can store timestamp values in another column of type of binary(8). If only values stored in such column origin from timestamp columns from one database - they will be unique. True timestamp values are also unique, of course.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

countrymeisterAuthor Commented:
which brings be back to my question with my columns -  MinTimeStamp and MaxTimeStamp.
They do store timestamp vlaues but in binary(8), so how do I get the time in readable format
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I have to back up maradam's statement: the data type timestamp is NOT to start a date/time value, but a kind of sequential stamp.

if you want a date/time, use the data type datetime, and NOT timestamp.
to fill in the current date/time value, you put in getdate() for example...

countrymeisterAuthor Commented:
I am so confused. help me out
Is it safe to state that if a a field is defined as Binary(8) and no matter what it is called by the Column name, it stores some binary value and has nothing to do with timestamp.
Meaning I cannot store a timestamp vlaue into a binary(8) field
Guy Hengel [angelIII / a3]Billing EngineerCommented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.