11

What is the underlying datastructure of datetime values stored in SQL Server (2000 and 2005 if different)? Ie down to the byte representation?

Presumably the default representation you get when you select a datetime column is a culture specific value / subject to change. That is, some underlying structure that we don't see is getting formatted to YYYY-MM-DD HH:MM:SS.mmm.

Reason I ask is that there's a generally held view in my department that it's stored in memory literally as YYYY-MM-DD HH:MM:SS.mmm but I'm sure this isn't the case.

Ray
  • 3,468
  • 8
  • 26
  • 27

1 Answers1

18

It's stored as an 8 byte field, capable of a range from 1753-01-01 through 9999-12-31, accurate to 0.00333 seconds.

The details are supposedly opaque, but most resources (1), (2) that I've found on the web state the following:

The first 4 bytes store the number of days since SQL Server's epoch (1st Jan 1900) and that the second 4 bytes stores the number of ticks after midnight, where a "tick" is 3.3 milliseconds.

The first four bytes are signed (can be positive or negative), which explains why dates earlier than the epoch can be represented.

Roger Lipscombe
  • 89,048
  • 55
  • 235
  • 380
  • 8
    That's not correct. From the linked article: Actually, SQL Server does store there the clock-ticks since midnight. Each clock-tick is equivalent to 3.33 milliseconds. That’s also the reason why the DATETIME datatype has an accuracy of one three-hundredth of a second. This, again, is correctly stated in BOL. – ctusch May 12 '13 at 13:19
  • 3
    @ctusch, I've updated the answer (after almost 7 years!) to correct the size of the "ticks". – Roger Lipscombe Mar 07 '17 at 10:45
  • 1
    Interestingly, the SQL Server documentation uses the term "accuracy". It *should* say "precision". – Roger Lipscombe Oct 23 '17 at 07:28
  • Update: SQL Server 2019 uses 8 bytes with a stated precision of ".000, .003, or .007 seconds". MS documentation (https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver15) doesn't give the breakdown per se. But the individual date and time types use 3 and 5 bytes respectively with the time precision given as 100ns. – Christopher J Smith Jul 30 '21 at 14:08