How SQL Server Stores DATETIME2, DATE, and TIME

In the previous post I took a DATETIME apart byte by byte: two 4-byte integers, a date counted from 1900 and a time counted in awkward three-hundredths of a second. That awkward tick is exactly what Microsoft set out to fix when DATETIME2 arrived in SQL Server 2008. The newer family, DATETIME2, DATE, and TIME, throws out the 1900 epoch and the 1/300 second and replaces them with a cleaner design that, in a pleasant surprise, can store more while using less space.

This post does for the modern types what the last one did for DATETIME. Most of the technique carries straight over, so if you have not read that one the binary-conversion trick will make more sense if you start there.

A New Epoch and a Smaller Date

The first thing that changes is where time begins. DATETIME counts days from 1900-01-01 and uses a signed integer so it can reach backward to 1753. DATE and DATETIME2 count days from 0001-01-01 using an unsigned 3-byte integer, which is why their range runs from the year 1 all the way to 9999-12-31.^[2]

Three bytes is the smallest reproducible piece of the new design, so it makes the cleanest demonstration. A DATE is nothing but a little-endian day count from year one:

The epoch is three zero bytes. Add a day and the low byte becomes 01, written first because the integer is stored little end first, exactly the byte order we untangled for DATETIME. Three bytes hold values up to 16,777,215, and the journey from year 1 to year 9999 is only about 3.65 million days, so a 3-byte date has room to spare while still being a byte smaller than DATETIME’s 4-byte date.

Time in Powers of Ten

The bigger improvement is the time portion. DATETIME measured time in ticks of 1/300 of a second, which is where its odd millisecond rounding came from. TIME and DATETIME2 measure time in plain decimal fractions of a second chosen by the type’s scale. At scale 7, the default, a tick is 100 nanoseconds.^[3] At scale 3 it is one millisecond. Because the units are powers of ten, .999 stores as .999 and the “rounds to .003 and .007” surprise simply does not happen.

The scale also decides how many bytes the time needs, and this is where the storage gets interesting:

A standalone TIME column is just that time integer, so it occupies 3, 4, or 5 bytes depending on the scale you ask for. A DATETIME2 is the time integer followed by the same 3-byte date from above, which makes its total storage the sum of the two parts.

The Payoff: More Range, Less Space

Put the date and time sizes together and DATETIME2 lands at 6, 7, or 8 bytes^[1]:

Read that table next to DATETIME and the trade becomes lopsided. For the same 8 bytes DATETIME spends, datetime2(7) gives you the full year-1-to-9999 range and 100-nanosecond precision instead of 1753 and 3.33 milliseconds. If you only need millisecond precision, datetime2(3) delivers it with the full range in 7 bytes, one byte less than DATETIME. And if you are storing whole seconds, datetime2(0) does it in 6. There is no width at which DATETIME wins, which is exactly why Microsoft has recommended the newer types for new work for years.

The one practical catch is the default. Write datetime2 with no scale and you get scale 7, the full 8 bytes. If you know you only need milliseconds, say datetime2(3) on purpose and bank the byte.

The Physical Layout

The byte-level order matches DATETIME’s, and you can confirm it with the same DBCC PAGE technique from the previous post: create a one-column table in tempdb, find its data page with sys.dm_db_database_page_allocations, and dump the page with DBCC PAGE (..., 3) WITH TABLERESULTS after turning on trace flag 3604.

When you read the record’s memory dump, the DATETIME2 lays out as the time integer first and the 3-byte date second, each stored little end first, just like DATETIME. The difference is only in the widths and the meanings: the time integer is now a power-of-ten count instead of three-hundredths, and the date is a 3-byte count from year one instead of a 4-byte count from 1900. The leading header bytes of the record behave the same way, which is why a script that cracks these values reaches into the dump at a fixed offset rather than reading from the very front.

DATETIMEOFFSET, if you reach for it, is simply a DATETIME2 with two more bytes on the end holding the UTC offset in signed minutes, so it runs 8, 9, or 10 bytes by the same scale rules.

The Takeaway

DATETIME2 is not just “DATETIME with more decimals.” It is a different shape: a 3-byte date counted from the year 1, a time measured in clean powers of ten, and a total width you control with the scale. The payoff is concrete. You get a wider range, you lose the 3.33 millisecond rounding, and depending on the precision you actually need you spend the same eight bytes or fewer. For anything new, that is the easy call.

If you want to see where this design came from, the contrast with the older type is the whole story, and I walked through it in the DATETIME storage post.

Do you still reach for DATETIME out of habit, or have you switched your standards over to DATETIME2? I am curious how many shops have actually made the move. Find me on Bluesky or LinkedIn.

References

1. datetime2 (Transact-SQL) – Microsoft Learn. Range from 0001-01-01 to 9999-12-31, fractional-second precision, and storage size of 6, 7, or 8 bytes by precision. ↩
2. date (Transact-SQL) – Microsoft Learn. The 3-byte storage and the 0001-01-01 epoch. ↩
3. time (Transact-SQL) – Microsoft Learn. Scale, the 3, 4, or 5 byte storage sizes, and accuracy of 100 nanoseconds. ↩