views:

289

answers:

1

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.

+5  A: 

It's stored as an 8 byte field. The first 4 bytes stores the number of days since SQL Server's epoch (1st Jan 1900). The second 4 bytes stores the number of milliseconds after midnight.

Roger Lipscombe