asked
3 Bytes for DATE how?
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][1] states the date range stored is from 0001-01-01 to 9999-12-31; approximately 3,652,500 days. Year 10k problem, anybody? [1]:
https://docs.microsoft.com/en-us/sql/t-sql/data-types/date-transact-sql
Y2K was not good for the company I worked at at the time - we were a small consultancy that was focused on implementing new business process / document management / imaging systems, and didn't have any clients with non-y2k compliant systems (at least, not ones we had put in), so no nice maintenance / upgrade contract work. The company shrank by about 25%-30% during 1999-early2000.
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
Yup. Apart from anything else, it makes the whole business of adding numbers of days to dates a lot easier if it's just a zero- or one-based integer being stored...
17 People are following this question.
