Binary Date Conversions

  • drew.allen

    SSC Guru

    Points: 76739

    Comments posted to this topic are about the item Binary Date Conversions

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • TomThomson

    SSC Guru

    Points: 104773

    Amusing question, but a rather incomplete explanation.

    The storage format ensures that the binary max and the datetime max will be the same date (as the datetime and its binary conversion have the same order).  However that elimiates only two of the four rows.   So now we need to know which of the remaining two rows will be maximum in binary order for date, and the only way to do that is to compute enough of the binary representations of the two candidate dates to see. The explanation ought to suggest at least one means of doing that.  

    Of course for this question the easiest way would be just to run the code - and I suspect quite a few of the 75% of answers (up to now) that are correct are the result of that approach.

    Tom

  • drew.allen

    SSC Guru

    Points: 76739

    TomThomson - Monday, November 20, 2017 7:37 AM

    The storage format ensures that the binary max and the datetime max will be the same date (as the datetime and its binary conversion have the same order).  However that elimiates only two of the four rows.   So now we need to know which of the remaining two rows will be maximum in binary order for date, and the only way to do that is to compute enough of the binary representations of the two candidate dates to see. The explanation ought to suggest at least one means of doing that.  

    I was aware that the conversion of datetime data to binary conserved the sort order, so I was very surprised to find the same was not true for converting date data to binary.  Itzik Ben Gan has a method of finding the most recent non-null value of a field based on sorting another field that involves converting both fields to binary and finding the running MAX/MIN of that combination.  I kept getting unexpected results when I tried this method using a DATE field as the sort field.  I wrote this question so that other people would be aware of this behavior.

    The details on how datetime data is represented in SQL is well documented, but I've not found information about how date data is represented.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • TomThomson

    SSC Guru

    Points: 104773

    drew.allen - Monday, November 20, 2017 10:45 AM

    The details on how datetime data is represented in SQL is well documented, but I've not found information about how date data is represented.

    Drew

    It's an integer (reprenting the number of dats since 0001-01-01).  Bits within each octet having decreasing significance from left to right . The Octets have increasing significance from left to right (I think - I haven't got around to doing enough work to confirm the octet order other that that the first - leftmost - octet is the least significnt octet of the date even though it would only take  few lines of code to prove or disprove that order ; but I'd be surprised if the format was based on old fashioned 16 bit units, which went out of fashion a long time ago). 
    Anyway, the leftmost octet (least significant when thinking of a date, but most significant when thinking of a binary(n) value) of the representation of somedate can be caculated as
    datediff(dd,cast('0001-01-01' as date),somedate)%256
    and that's what I used to choose between the remaining two options in your question (the two dates were so close together that there was no chance of that ocytet being the same in both, so that was sure to determine it - with dates more widely separated the odds on the two ls octets being different for two rndom dates are 255:1).

    Tom

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71852

    This was quite a head-scratcher, thanks Drew
    learned something interesting here
    Thanks for the added detail, Tom.

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply