This article contains examples of converting a datetime value to a datetimeoffset value in SQL Server.
When you convert a datetime value to datetimeoffset, the resulting value will depend on the fractional seconds precision that you assign to datetimeoffset, as well as any time zone offset you specify.
The datetime data type has a maximum of 3 digits for its fractional seconds part. Its accuracy is rounded to increments of .000, .003, or .007 seconds.
The datetimeoffset data type, on the other hand, allows you to specify a fractional seconds precision from 0 to 7. If you don’t specify this, it will use 7 (the default). It also has a time zone offset and can preserve any offsets in the original value. However, datetime has no time zone awareness, so there are no existing values to preserve. In this case, the time zone offset defaults to +00:00.
SQL Server actually has the TODATETIMEOFFSET() function, which is specifically designed to convert a date/time value to datetimeoffset and add a time zone offset. However, there’s a subtle detail to be aware of when using this function, and I explain this below (with examples).