How SQL Server stores data types: dates and times

This post dives into how SQL Server stores date and time data types in memory and on disk.

But first, a note about endianness: CPUs manufactured by Intel and other vendors process binary values in reverse order (known as little-endian), with the least significant byte in a binary value first, and the most significant (i.e. the first byte) last. There are technical and historical reasons for this which are not important for this post, but it does mean that when you want to read values back you will have to flip the order of bytes to use a hex calculator.

Dates

DATE is the byte-reversed number of days since the year 0001-01-01, stored as three bytes. It goes up to 9999-12-31, which is stored as 0xDAB937. You can check this value by reversing the bytes and sticking them into a hex calculator. 37 B9 DA equals 3,652,058, which is the number of days since 0001-01-01.

If you try to cast 0xDBB937 as a DATE value (by incrementing the least significant bit DA by 1), it will throw a conversion error. There is obviously some overflow detection that protects against corruption in a date type.

Times

TIME works slightly differently, as noted in a previous blog post. It can store the number of fractions of a second (known as the precision) — with a length ranging from 0 to 7 — from midnight up to and including the final fraction of a second before midnight. In normal operations, the precision is stored in the column metadata, and you will not see it if you look at the table directly. However, if you cast the value to VARBINARY for example, the precision is prefixed as a single byte before the value, ranging from 0x00 to 0x07.

There are 1 billion (1,000,000,000) nanoseconds in a single second, and TIME(7) has a granularity of 100 nanoseconds, or ten million possible values per second. In a 24-hour period there are 864,000,000,000 possible values, so if you don’t need that kind of granularity you might be better off choosing a smaller precision.

Keep in mind that more than one length can be stored in the same number of bytes, as shown below:

• TIME(7) – 5 bytes – is the byte-reversed number of tenths of a microsecond (0.0000001)
• TIME(6) – 5 bytes – is the byte-reversed number of microseconds (0.000001)
• TIME(5) – 5 bytes – is the byte-reversed number of tens of microseconds (0.00001)
• TIME(4) – 4 bytes – is the byte-reversed number of tenths of a millisecond (0.0001)
• TIME(3) – 4 bytes – is the byte-reversed number of milliseconds (0.001)
• TIME(2) – 3 bytes – is the byte-reversed number of tens of milliseconds (0.01)
• TIME(1) – 3 bytes – is the byte-reversed number of tenths of a second (0.1)
• TIME(0) – 3 bytes – is the byte-reversed number of seconds (1)

Dates and times

We can think of DATETIME2(n) as DATE and TIME jammed together in the same field (though the time value is first because of endianness). The precision is again encoded in the value as a prefix if you cast it as binary. In the table itself, the prefix is excluded because the column metadata already contains the precision.

Let’s look at today’s date of April 22, 2020, at 5 minutes after 10 in the morning. We can calculate that it is the 737,536th day since January 1, 0001, and approximately 36,310 seconds have passed. Depending on the precision, the value would look something like this (remember the time is on the left, and the date is on the right):

What about DATETIME?

This is a curious data type, because despite taking up eight bytes like DATETIME2(7), it has a granularity of just 3 to 4 milliseconds, which means it will round up (or down) any input value to the nearest 0, 3, or 7 milliseconds. How is this data type stored, and why is it so inefficient when compared to DATETIME2(n)? And, why does the range for a DATETIME data type start in the year 1753?

The answers to these questions may surprise you. For starters, the value is not byte-swapped. The first four bytes of DATETIME represent the date, and the second four bytes represent the time. The “starting point,” or the place where the binary is 0x0000000000000000, is midnight on January 1st, 1900. For any date after 1900-01-01, the date portion of the binary is incremented by one. Using our date of 2020-04-22 as an example, we can calculate that 43,941 days have passed since 1900-01-01, and converting that to hex gives us 0xABA5.

Indeed, the statement SELECT CAST(0x0000ABA500000000 AS DATETIME) returns 2020-04-22 00:00:00.000 as expected.

So what about dates prior to 1900? This is where it gets interesting. One day prior, namely midnight on December 31st, 1899, has a binary value of 0xFFFFFFFF00000000. That’s the maximum possible value for a four-byte value, and equates to around 2 billion possible values. From what I remember in my 1992 high school computer science class, this is a binary arithmetic operation called two’s complement. This technique is used for calculating positive and negative values, so if 0 is our starting point (0x00000000), -1 would be the maximum possible value for the number of bytes, which in this case is 0xFFFFFFFF.

Two days prior to 1900-01-01 would then be 0xFFFFFFFE, and sure enough, the statement SELECT CAST(0xFFFFFFFE00000000 AS DATETIME) returns 1899-12-30 00:00:00.000 as expected. And now, because the DATETIME data type only goes back as far as the year 1753, we don’t have to worry about overflowing this value.

(The reason for SQL Server using January 1st, 1753 as the earliest possible date, is best explained in this Stack Overflow answer. When the British switched to the Gregorian calendar, they lost 12 days in 1752, so as Kalen Delaney explains, “[…] the original Sybase SQL Server developers decided not to allow dates before 1753”. As the answer also states, DATE and DATETIME2 are based on the Gregorian calendar, so you must keep this in mind for dates prior to that calendar’s adoption.)

The time portion of DATETIME — namely the second four bytes — increments by one at a time as well, from 0x00000000 to 0x018B81FF. However because DATETIME rounds to the nearest 0, 3 or 7 milliseconds, the incrementing value might be 3 or 4 milliseconds. The maximum value for a time is 23:59:59.997, which matches to that binary value of 0x018B81FF. Working our way backwards, 0x018B81FE (one less than the previous value) is equivalent to 23:59:59.993, and 0x018B81FD works out to 23:59:59.990. Using millisecond values ending in 0, 3 and 7, we get back all the way to 0x00000000 which represents 00:00:00.000.

It’s strange to me that with two billion possible values per four-byte segment (ignoring negative values), Sybase — who originally wrote SQL Server before the product was purchased and rewritten by Microsoft — opted to store the date and time in this fashion. There are only 86.4 million milliseconds in a 24-hour period, and 3.6 million days in 10,000 years, so those could easily have fitted into the two four-byte segments that make up this data type (you can even save a byte and have a more accurate value using the 7-byte wide DATETIME2(3) data type).

This 0, 3 and 7 increment is very odd. Perhaps Jeff Moden has some thoughts. And speaking of thoughts, please leave yours in the comments below.

Photo by Jon Tyson on Unsplash.