• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 294
  • Last Modified:

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)

Example
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
0
countrymeister
Asked:
countrymeister
  • 3
  • 2
  • 2
2 Solutions
 
maradamCommented:
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.
0
 
countrymeisterAuthor Commented:
Maraadam

So a column defined as binary(8) though named timestamp stores a unique binary value
0
 
maradamCommented:
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.
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
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
0
 
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...

0
 
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
correct...
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now