Reading timestamp type

Posted on 2005-04-29
Last Modified: 2010-03-19
A select of a timestamp field just returns <binary>. How do I have a look at the values in human-readable form?


Question by:RichardFox
    LVL 68

    Expert Comment

    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.
    LVL 44

    Expert Comment

    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
    LVL 34

    Expert Comment

    by:Brian Crowe
    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.

    Author Comment

    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?
    LVL 68

    Accepted Solution

    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.
    LVL 13

    Expert Comment

    Timestamp is not "unreadable". EM doesn't display any binary columns. Run you query in isqlw.exe, and you will see them.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now