For me it should be Localtime stored DateTimeOffset OR UTC in DateTime(2). Not UTC in DateTimeOffset. But I might be wrong.
Is there any logical reason to store UTC in a DateTimeOffSet DB.Column? If so, it will always look like " +00:00" Makes no sense. Does a DateTimeOffset Column consume more than 8 bytes?
UTC being stored in a datetimeoffset
makes sense, provided you're going to be storing other timezones too. If every time you're going to store is UTC, then you might as well use datetime2
(or possibly smalldatetime
depending on your accuracy requirements).
Even if all your data is for one country, but that country observes DST, then it might be worth using datetimeoffset
. then times in the summer might be UTC +01:00 and in the winter UTC +00:00. But that depends if knowing if DST is in effect is important (it might not be).
Note, however that datetime2
is a smaller datasize than a datetimeoffset
, so you do need to take that in considering when working with much larger result sets. If you look at Date and Time data types you'll note that datetime2
uses 6-8 bytes however, datetimeoffset
uses 8-10 bytes (so the smallest size datetimeoffset
is as large as the largest datetime2
).