I prefer an approach of having a table of start and end dates of the daylight savings time for all the years of interest for all the regions/locations of interest in a table. Even so, if you want to be completely general, it is a very hard problem for a number of reasons:
1. Daylight savings time is observed by some countries/regions and not by others.
2. Even in countries (such as USA) that observe daylight savigns time, some regions and states (Arizona, parts of Indiana for example) do not observer daylight savings time.
3. The date on which the daylight savings time starts and ends varies by country/region.
4. Even in a given region, the start and end dates is not easily determined via calculations. For example, an act of Congress expanded the daylight savings date range starting in 2007.
In a simplified case, for example, let us say you are interested in only the eastern time zone of USA for 2006 through 2013 you could do this as shown below.
First, create a table of daylight savings start and end times for your region of interest. For USA (excluding Arizona etc.) this would be as follows:
create table dbo.DaylightSavingsDates
insert into dbo.DaylightSavingsDates values
(2013,'USA','20130310','20131103');Now you can join with that table to find the UTC time as shown below. Here I am hardcoding the offsets. You could calculate that using the diff between local time and UTC time (but be sure to account for whether you are in daylight savings time or standard time when the offset is calculated).
create table #tmpDates(localdate datetime);
insert into #tmpDates values ('2006-04-07T17:03:05.000'),('2013-02-01T11:00:07.000');
declare @stdhours int = 5;
declare @daylighthours int = 4;
case when localdate >= startDate and localdate < endDate then @daylighthours
else @stdhours end,
) as UTCDate
inner join dbo.DaylightSavingsDates d on
d.yr = YEAR(t.localdate);
This is not perfect because at 2:00 AM (in the US) when the daylight savings time changes, this will still get confused.