Today I went for investigating the internal storage of DATETIMEOFFSET datatype. What I found out was that for a datetimeoffset value with precision 0 (seconds only), SQL Server need 8 bytes to represent the value, but stores 9 bytes. This is because SQL Server add one byte that holds the precision for the datetimeoffset value. This is true when converting the value to it’s binary representation. In a table, the prefix is stored in the metadata, not with the value itself.
Start with this very simple repro
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 63,244 seconds to midnight, you get 17:34:04, which is the correct UTC time. It is the UTC time that is stored, and the local time can be found by adding “UTC offset” minutes to that value. 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.