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.
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:
- Start date: We evaluate the day of the week corresponding to the first day of the month and find the second Sunday of March using a Case statement
- End date: We evaluate the day of the week corresponding to the first day of the month and find the first Sunday of March using a Case statement
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 for Daylight Savings Time
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 for Daylight Savings Time
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.
Sample Execution
declare @DLSStart smalldatetime, @DLSEnd smalldatetime, @DLSActive tinyint
set @DLSStart = (select MSSQLTIPS.dbo.fn_GetDaylightSavingsTimeStart(convert(varchar,datepart(year,getdate()))))
set @DLSEnd = (select MSSQLTIPS.dbo.fn_GetDaylightSavingsTimeEnd(convert(varchar,datepart(year,getdate()))))
if @Date between @DLSStart and @DLSEnd
begin
set @DLSActive = 1
end
else
begin
set @DLSActive = 0
end
select @DLSActive
Next Steps
- Read additional information on creating functions in SQL Server 2000 and SQL Server 2005
- Read about the New Daylight Savings Time rules from the National Institute of Standards and Technology
