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.
Start with this very simple repro
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.