The internal storage of a DATETIMEOFFSET value
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'
Now we are going to copy and paste these binary values and investigate which value is representing what time part.
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