How SQL Server Stores a DATETIME, Down to the Bytes

Every DBA eventually asks the same odd little question. You insert '2024-03-14 09:26:53.999', read it back, and it says 09:26:54.000 instead. Store a value ending in .456 and it comes back as .457. Nothing is broken, nobody lost data, but the value you stored is not quite the value you typed. The usual answer is “DATETIME only has about 3.33 millisecond precision,” which is true and also completely unsatisfying. Why 3.33? Where does that number come from?

It comes from the bytes. A DATETIME is just two 4-byte integers sitting next to each other, and once you can read those eight bytes the rounding stops being a mystery and becomes arithmetic. This post takes a DATETIME apart two ways: first with a one-line conversion you can run anywhere, and then by reading the actual bytes off a data page so you can see how the engine really lays it out.

The Two-Integer Layout

A DATETIME occupies exactly 8 bytes, split straight down the middle:

• Four bytes for the date as a signed integer counting whole days from the epoch 1900-01-01. Positive numbers move forward, negative numbers move backward, which is how the type reaches all the way down to its minimum of 1753-01-01.
• Four bytes for the time as an integer counting ticks of one three-hundredth of a second since midnight.

That tick size is the whole story behind the rounding. One tick is 1/300 of a second, which is 3.3333... milliseconds. Because the time is stored as a whole number of those ticks, the only millisecond values a DATETIME can represent end in 0, 3, or 7.^[1] Ask for .999 and the engine rounds to the nearest tick, which lands on .000 of the next second. The famous “rounds to .003 and .007” behavior is not a rule someone invented; it is just what you get when you measure time in thirds of a hundredth of a second.

Proving It Without Any Special Permissions

You do not need DBCC anything to see the two integers. Converting a DATETIME to binary(8) shows you the raw value, and unlike reading a page it works on any database where you can run a SELECT. Start with the simplest possible cases:

Which gives you:

Read those eight bytes as two integers and they decode themselves. The epoch is all zeros: zero days, zero ticks. Adding a day flips the first half to 0x00000001 while the time stays zero. Noon leaves the date at zero and sets the time to 0x00C5C100, which is 12,960,000 in decimal. Divide by 300 and you get 43,200 seconds, exactly twelve hours. The last value is just the two halves combined: one day and one noon.

The rounding falls out of the same arithmetic. Watch a single tick appear and disappear:

A millisecond of .001 is 0.3 of a tick, closer to zero than to one, so it rounds down to .000 and the time integer stays at zero. .002 is 0.6 of a tick, closer to one, so the integer becomes 0x00000001 and reads back as .003. And .999 of a second is 299.7 ticks, which rounds up to a full 300 ticks, so the time integer becomes 0x0000012C (300 in decimal) and the value reads back as 00:00:01.000, one whole second. No magic, just rounding to the nearest three-hundredth.

Reading It Off the Page

The binary(8) view is honest about the values but tidy about the order: it presents the date first and the time second, big end first, the way you would write the number on paper. The data page does not store it that way. To see the real physical layout you have to read the bytes where they actually live, and that means DBCC PAGE.

The approach is to create a tiny table, find its data pages, dump them, and pull the bytes back out. Finding the pages is a job for sys.dm_db_database_page_allocations, the modern replacement for the old DBCC IND^[2]:

With a file and page number in hand, dump the page. Trace flag 3604 redirects DBCC output to the client instead of the error log,^[3] and dump style 3 gives you the per-row detail including a raw memory dump of each record. WITH TABLERESULTS turns that output into a result set you can shred:

Among the rows that come back is one whose Object column reads Memory Dump @0x... and whose VALUE is the record’s bytes laid out in 4-byte groups. For our single-column row it looks like this:

Now line that up against what binary(8) told us the value should be: one day, one noon, or 0x0000000100C5C100. The bytes on the page are 00c1c500 and 01000000, and neither half matches that order. Two things are happening at once.

First, the record starts with a few header bytes, so the date and time do not begin at the very front of the dump. The leading group, 10000c00, is the row’s status bits and the length of its fixed-data section, not part of the DATETIME at all. That is why the script that inspired this post reaches into the dump string with SUBSTRING at a fixed offset rather than reading from the start.

Second, and more interesting, the two integers are stored little end first, and the time comes before the date. The group 00c1c500 is the time integer with its bytes reversed: read it back to front as 00 c5 c1 00 and you are back to 0x00C5C100, our 12,960,000 ticks of noon. The group 01000000 is the date integer, also reversed, which flips back to 0x00000001, one day. So on disk a DATETIME is physically time first, then date, each one little-endian, which is the exact mirror image of the clean date | time picture that CONVERT(binary(8)) hands you.

That byte reversal is why the original script does all of its bit-masking arithmetic. Code like (b & -16777216) / 16777215 repeated across four masks is not decoding anything clever; it is swapping a little-endian 4-byte integer back into a normal one so it can be fed to DATEADD. Once the bytes are in the right order, reconstructing the value is exactly what you would expect: DATEADD(DAY, @date_int, '1900-01-01') for the date, and DATEADD(MILLISECOND, @time_ticks * 3.33333333, '1900-01-01') for the time of day.

What the Bytes Buy You

This is more than a parlor trick. Once you can see the layout, several DATETIME quirks stop needing memorization:

• The 3.33 ms rounding is the tick size, not a bug. If your application genuinely needs millisecond fidelity, that is a signal to move off DATETIME.
• The 1753 lower bound is just how far a signed 4-byte day count reaches backward from 1900 before you would rather not deal with calendar reform math.
• Sorting and date math are cheap because comparing two DATETIME values is, underneath, comparing two pairs of integers.

It also sets up the obvious next question. If DATETIME is a fixed eight bytes anchored at 1900 with this awkward tick, what did Microsoft do differently with the newer types? DATETIME2, DATE, and TIME use a different epoch, variable precision, and can actually take less space than DATETIME while storing more. I take those apart the same way in the follow-up post on DATETIME2 storage.

A note on running the page-reading half yourself: do it in tempdb or another scratch database, since DBCC PAGE is a read-only diagnostic but trace flags and page dumps are not something to point at a busy production box without thinking. The CONVERT(binary(8)) half is perfectly safe anywhere.

Have you ever been bitten by DATETIME rounding in a way that took real effort to track down? I would like to hear the war story. Find me on Bluesky or LinkedIn.

References

1. datetime (Transact-SQL) – Microsoft Learn. Storage size, range from 1753-01-01 to 9999-12-31, accuracy of one three-hundredth of a second, and the rounding to .000, .003, and .007. ↩
2. sys.dm_db_database_page_allocations (Transact-SQL) – Microsoft Learn. The supported replacement for DBCC IND when enumerating the pages allocated to an object. ↩
3. Trace flag 3604 – Microsoft Learn. Redirecting DBCC diagnostic output to the client session. ↩