The internal storage of a DATETIMEOFFSET value
- by Peter Larsson
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
