How to check the timezone for a given datetime?
This is neither technically nor even conceptually possible, due to the nature of what timezones are.

It should be noted that timezones are not simply the offset from UTC / GMT (i.e. the "+" or "-" value). The "offset" is a property of the timezone, but it does not, and cannot, determine the timezone since:


several timezones can share the same offset
timezones can come and go and can even shift their offset
different regions can change their timezones


Timezones are determined by governments. For example, in the U.S.A., there are primarily 4 timezones for the contiguous 48 states. But those same offsets are also used by other regions that probably have different laws regarding when Daylight Savings starts and ends, or if they even have a Daylight Savings period. And in fact, there are two extra timezones for the contiguous 48 states to handle differences for 2 states: one for Indiana and one for Arizona.

Daylight Savings start and end times change over time. The Indiana and Arizona links above discuss this for those two states. In the United States, the DST start and end dates changed in 2007, but this didn't affect other regions using those same offsets. And the DST offset isn't always 1 hour (mentioned in the prior link).

So, with:


a DATETIME value alone you have no basis for determining either the Offset or the Timezone (even if you had a Timezone database)
a DATETIME value and an OFFSET--whether stored separately or combined in a DATETIMEOFFSET datatype--you have the OFFSET (obviously) but cannot determine the exact Timezone, though it could be narrowed down to a few, but only if you had a table of which Timezones participated in that Offset at that particular moment in time (see below for sources of such data). With only the Offset, the DATETIME value can be converted to UTC, but not to another Offset without first having a Timezone database and knowing which specific Timezone you are converting it to, which will indicate the Offset for that particular point in time. (By saying it cannot be converted to another Offset, I mean in any meaningful way; clearly the Offset value can be adjusted, but that doesn't help much if you don't know what to adjust it to)
a DATETIME value and a Timezone (name) then you can determine the Offset by looking up what the Offset was at that point in time (see below for sources of such data)


Unfortunately this Timezone data is not built into SQL Server. But you can get it from the following sources (and possibly others):


Internet Assigned Numbers Authority (IANA)
TimeZoneDB