Daylight Savings Time Functions in SQL Server

Problem
With the new rules from the National Institute of Standards and Technology on the Daylight Savings Time calculations, a new set of logic is need to determine if daylight savings time is in effect. Since the new rules have recently been applied to our servers, is there a way in SQL Server to calculate when Daylight Savings Time starts and ends for a given year? Are any built-in functions available or do I need to build a custom solution?

Solution
In our particular case we have discrepancies between vendor software and our own monitoring software and have to account for time differences in six different time zones. In order to calculate the Eastern Time equivalent of the reported time, I have to know whether the site participates in Daylight Savings Time as well as whether Daylight Savings Time is currently in effect. To accomplish this, I created functions that return the start and end dates for Daylight Savings Time.

The first step, obviously, is to pass the year you want the start and end dates for, so the first few lines show the declaration of the function:

Next, we declare a smalldatetime variable with either '03/01/" plus the year or "11/01/" plus the year as the date, since we know that Daylight Savings Time always starts on the second Sunday of March and ends on the first Sunday of November:

Once we have the first day of the month, the next step depends on what we're looking for:

Undoubtedly you noticed that I used the DATEADD(hour,"DATE") function instead of DATEADD(day,"DATE"). Since Daylight Savings Time starts and ends at 02:00 I wanted the date returned from the function to be the actual time. If you don't need that kind of granularity then DATEADD(day,"DATE") will work fine.

Here are the complete sets of Daylight Savings Time code to determine the start date and end date:

Start Date Function

CREATE function [dbo].[fn_GetDaylightSavingsTimeStart]
(@Year varchar(4))
RETURNS smalldatetime
as
begin
declare @DTSStartWeek smalldatetime, @DTSEndWeek smalldatetime
set @DTSStartWeek = '03/01/' + convert(varchar,@Year)
return case datepart(dw,@DTSStartWeek)
when 1 then
dateadd(hour,170,@DTSStartWeek)
when 2 then
dateadd(hour,314,@DTSStartWeek)
when 3 then
dateadd(hour,290,@DTSStartWeek)
when 4 then
dateadd(hour,266,@DTSStartWeek)
when 5 then
dateadd(hour,242,@DTSStartWeek)
when 6 then
dateadd(hour,218,@DTSStartWeek)
when 7 then
dateadd(hour,194,@DTSStartWeek)
end
end

End Date Function

CREATE function [dbo].[fn_GetDaylightSavingsTimeEnd]
(@Year varchar(4))
RETURNS smalldatetime
as
begin
declare @DTSEndWeek smalldatetime
set @DTSEndWeek = '11/01/' + convert(varchar,@Year)
return case datepart(dw,dateadd(week,1,@DTSEndWeek))
when 1 then
dateadd(hour,2,@DTSEndWeek)
when 2 then
dateadd(hour,146,@DTSEndWeek)
when 3 then
dateadd(hour,122,@DTSEndWeek)
when 4 then
dateadd(hour,98,@DTSEndWeek)
when 5 then
dateadd(hour,74,@DTSEndWeek)
when 6 then
dateadd(hour,50,@DTSEndWeek)
when 7 then
dateadd(hour,26,@DTSEndWeek)
end
end

An example of where you could use this logic is in a stored procedure that gathers database entries from different time zones, but you need to convert the time to your particular time zone (Eastern Time in our case). You would pass the current date and time into the stored procedure, in which three variables are created: @DLSStart (smalldatetime), @DLSEnd (smalldatetime), and @DLSActive tinyint. The stored procedure would extract the year from the current date, set @DLSStart and @DLSEnd to their respective dates using the two functions. Then a simple comparison is made: if the current date is between @DLSStart and @DLSEnd then set @DLSActive = 1, otherwise set @DLSActive = 0.

In addition, if you have the Time Zone and Daylight Savings Time setting saved, you can use the Bitwise And operator to determine whether Daylight Savings Time should be compensated for in the time comparison.