Peter Larsson Blog

Patron Saint of Lost Yaks

The internal storage of a DATETIMEOFFSET value

by Peter Larsson in  ,

Today I went for investigating the internal storage of DATETIME2 datatype. What I found out was that for a datetime2 value with precision 0 (seconds only), SQL Server need 6 bytes to represent the value, but stores 7 bytes.
This is because SQL Server add one byte that holds the precision for the datetime2 value.

Start with this very simple repro


declare    @now datetimeoffset(7) = '2010-12-15 21:04:03.6934231 +03:30'

 
select     cast(cast(@now as datetimeoffset(0)) as binary(9)),
           cast(cast(@now as datetimeoffset(1)) as binary(9)),
           cast(cast(@now as datetimeoffset(2)) as binary(9)),
           cast(cast(@now as datetimeoffset(3)) as binary(10)),
           cast(cast(@now as datetimeoffset(4)) as binary(10)),
           cast(cast(@now as datetimeoffset(5)) as binary(11)),
           cast(cast(@now as datetimeoffset(6)) as binary(11)),
           cast(cast(@now as datetimeoffset(7)) as binary(11))


Now we are going to copy and paste these binary values and investigate which value is representing what time part.

Prefix  Ticks       Ticks         Days    Days    Suffix  Suffix  Original value
——  ———-  ————  ——  ——  ——  ——  ————————
0x  00  0CF700             63244  A8330B  734120  D200       210  0x000CF700A8330BD200
0x  01  75A609            632437  A8330B  734120  D200       210 0x0175A609A8330BD200
0x  02  918060           6324369  A8330B  734120  D200       210  0x02918060A8330BD200
0x  03  AD05C503        63243693  A8330B  734120  D200       210  0x03AD05C503A8330BD200
0x  04  C638B225       632502470  A8330B  734120  D200       210  0x04C638B225A8330BD200
0x  05  BE37F67801    6324369342  A8330B  734120  D200       210  0x05BE37F67801A8330BD200
0x  06  6F2D9EB90E   63243693423  A8330B  734120  D200       210  0x066F2D9EB90EA8330BD200
0x  07  57C62D4093  632436934231  A8330B  734120  D200       210  0x0757C62D4093A8330BD200


Let us use the following color schema
Red - Prefix
Green - Time part
Blue - Day part
Purple - UTC offset

What you can see is that the date part is equal in all cases, which makes sense since the precision doesn't affect the datepart.
If you add 63244 seconds to midnight, you get 17:34:04, which is the correct UTC time. So what is stored is the UTC time and the local time can be found by adding "utc offset" minutes.
And if you look at it, it makes perfect sense that each following value is 10 times greater when the precision is increased one step too.

//Peter