Does SQL Server store DateTime Offset Agnostic?

Question

I've been searching all morning and can't seem to get a handle on this (though I do have a few possible theories). It's also not impossible that this might be a duplicate but please take into account that all the questions I searched, didn't give a definitive answer but were rather too open to interpretation.

In SQL Server (>= 2012), a table column of type datetime, is it stored timezone offset agnostic or is how does it work? From my investigation, it would seem that datetimeoffset is the type that includes the offset with the date/time while datetime simply omits this?

When I read the data from the database, and use CONVERT( datetimeoffset, [My Column] ) it's giving me 2016-09-21 16:49:54.7170000 +00:00 while myself and the server are both in UTC +02:00 which reinforces my belief, am I correct?

What I'm trying to achieve, is allow data to be saved FROM various tz offsets (via a function), then saved into the database in UTC and finally convert the datetime value back to a (possibly different) offset. I don't care about DST / etc as the users browser will give me the current offset at the time of the saving and the viewing user will give me their tz offset at the time of viewing. For historic reports the exact time of day (DST dependent) is irrelevant.

Currently the database tables already use datetime as opposed to datetimeoffset; it's my observation that it's completely fine to continue with this, though at some point, it might be good to change to datetimeoffset in order to then have start recording the historic tz offset?

Any clarity will be greatly appreciated.

Answer 1

TL;DR; Yes. the DateTime (and DateTime2) data type is not time-zone aware.

The long version:

Official documentation of DateTime clearly states that the DateTime data type does not support time zone (nor daylight savings time). Same is true for DateTime2.

You can see in both pages there's a table that describes the data type's properties, and in that table, for both data types, the value for "Time zone offset aware and preservation" and for "Daylight saving aware" is "No".

Time zone offset aware and preservation     No
Daylight saving aware                       No

The description of DateTime is as follows:

Defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock.

The description of DateTime2 is as follows:

Defines a date that is combined with a time of day that is based on 24-hour clock.
datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision.

The only data type that is timezone aware is DateTimeOffset:

Defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock.

Btw, it is recommended to choose DateTime2 over DateTime, both by Microsoft official documentation:

Note
Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.

And by SQL Server professionals: Why You Should Never Use DATETIME Again!:

Datetime also have a bug/feature implicitly converting string literals of format yyyy-mm-dd / yyyy-mm-dd hh:mm:ss - Datetime will try to convert them using local settings, while Datetime2 will always convert them correctly. Check out this SO post about it.