November 19, 2017 at 12:09 am
Comments posted to this topic are about the item Binary Date Conversions
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 20, 2017 at 7:37 am
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
November 20, 2017 at 10:45 am
TomThomson - Monday, November 20, 2017 7:37 AMThe 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
November 20, 2017 at 12:05 pm
drew.allen - Monday, November 20, 2017 10:45 AMThe 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
November 21, 2017 at 1:50 am
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply