I'm having trouble understanding how the DATE data type storage is 3 bytes in SQL Server. The DATE data type stores only date values with a range of 0001-01-01 through 9999-12-31 and without hyphens. Therefore the possible range is between 00010101 and 99991231. That being the case what am I missing here? 1 Byte = 2^8 = 256 (2^8-1= 255) 3 Bytes = 2^24 = 16,777,216 = Largest value stored in 3 bytes The date 99991231 in binary = 101111101011011111010111111 = 27 BITS Greatest possible 3 Byte value: 16777216 Max DATE data type 3 Byte value: 99991231 Shouldn't storage require more than 3 Bytes?
The date data-type is in effect the number of days since the so-called epoch date / day zero, rather than an integer-type representation of the date such as you describe.. The [documentation] states the date range stored is from 0001-01-01 to 9999-12-31; approximately 3,652,500 days. Year 10k problem, anybody? :
Interesting! I have seen the Microsoft resource but was having trouble putting it together. To make sure I understand what you've explained, the date 0001-01-01 is stored as 1, for example? Because its the first possible value? Then SQL Server knows to up-convert? I had also found that perhaps I was looking at the storage incorrectly in that the month precedes the month preceeding the year.. like this: 01-01-0001 = 11110110100101010001 = 20 Bits 12-31-9999 = 101110111111110011111111 = 24 Bits Total Value Range between 01-01-0001 and 12-31-9999 However, what you're saying the correct storage method is a zero-day method that counts the total number of days between the min and max values like this, correct? 3652500 = 1101111011101110010100 = 3 Bytes