 |
|
Daylight Savings Time Functions in SQL Server
|
By: Tim Cullen | Read Comments (7) | Related Tips: More > Dates |
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:
| Start Date | End Date |
| CREATE function [dbo].[fn_GetDaylightSavingsTimeStart] (@Year varchar(4)) RETURNS smalldatetime as begin |
CREATE function [dbo].[fn_GetDaylightSavingsTimeStart] (@Year varchar(4)) RETURNS smalldatetime as begin |
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:
| Start Date | End Date |
| declare @DTSStartWeek smalldatetime, @DTSEndWeek smalldatetime set @DTSStartWeek = '03/01/' + convert(varchar,@Year) |
declare @DTSEndWeek smalldatetime set @DTSEndWeek = '11/01/' + convert(varchar,@Year) |
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
| Start Date | End Date |
| 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 |
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 |
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.
|
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
- Read more Function tips on MSSQLTips.com!
- See how Daylight Savings impacted other components in SQL Server:
Last Update: 11/13/2007
About the author
View all my tips
Print
Comments and Feedback:
| Monday, June 15, 2009 - 6:05:11 AM - jcelko | Read The Tip |
|
Wouldn't a simple lookip table be easier to maintain, portable instead of proprietary, standard instead of dialect and declarative instead of procedural code? Do remember the other times when DST rules changed in the US and Germany? The Look up table would have the date-time ranges and the UTC displacements for all the time zones -- just copy the DDL for the schema information table out of the the timezone section of the ANSI/ISO SQL Standard.
|
|
| Thursday, December 08, 2011 - 9:22:21 AM - adkeller | Read The Tip |
|
Thanks for the article! I believe the calculations are only valid for year 2007 and later. I wrote modified versions that will calculate historic start and end dates for DST. Of course most programming languages have a Date/Time object that can perform these calculations, and I believe SQL Server 2008 introduced better handling of timezones. And ideally the dates would be stored in UTC. But when converting dates in an old application on SQL Server 2005, these functions did the trick. ---------------------------------------- CREATE FUNCTION [dbo].[fn_GetDaylightSavingsTimeEnd] (@Year varchar(4)) RETURNS smalldatetime as BEGIN
DECLARE @DTSEndWeek smalldatetime
IF(@Year >= '2007') BEGIN -- First Sunday in November 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 ELSE IF(@Year >= '1966') BEGIN -- Last Sunday in October SET @DTSEndWeek = '11/01/' + convert(varchar, @Year) RETURN CASE datepart(dw,@DTSEndWeek) WHEN 1 THEN dateadd(hour,-166,@DTSEndWeek) WHEN 2 THEN dateadd(hour,-22,@DTSEndWeek) WHEN 3 THEN dateadd(hour,-46,@DTSEndWeek) WHEN 4 THEN dateadd(hour,-70,@DTSEndWeek) WHEN 5 THEN dateadd(hour,-94,@DTSEndWeek) WHEN 6 THEN dateadd(hour,-118,@DTSEndWeek) WHEN 7 THEN dateadd(hour,-142,@DTSEndWeek) END END
-- Daylight Savings did not exist. Return the smallest smalldatetime. RETURN CAST('1900-01-01 00:00:00.000' AS smalldatetime); END
----------------------------------------
CREATE function [dbo].[fn_GetDaylightSavingsTimeStart] (@Year varchar(4)) RETURNS smalldatetime as BEGIN
DECLARE @DTSStartWeek smalldatetime
IF(@Year >= '2007') BEGIN -- Second Sunday in March 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 ELSE IF(@Year >= '1987') BEGIN -- First Sunday in April SET @DTSStartWeek = '04/01/' + convert(varchar, @Year) RETURN CASE datepart(dw,@DTSStartWeek) WHEN 1 THEN dateadd(hour,2,@DTSStartWeek) WHEN 2 THEN dateadd(hour,146,@DTSStartWeek) WHEN 3 THEN dateadd(hour,122,@DTSStartWeek) WHEN 4 THEN dateadd(hour,98,@DTSStartWeek) WHEN 5 THEN dateadd(hour,74,@DTSStartWeek) WHEN 6 THEN dateadd(hour,50,@DTSStartWeek) WHEN 7 THEN dateadd(hour,26,@DTSStartWeek) END END ELSE IF(@Year >= '1966') BEGIN -- Last Sunday in April SET @DTSStartWeek = '05/01/' + convert(varchar, @Year) RETURN CASE datepart(dw,@DTSStartWeek) WHEN 1 THEN dateadd(hour,-166,@DTSStartWeek) WHEN 2 THEN dateadd(hour,-22,@DTSStartWeek) WHEN 3 THEN dateadd(hour,-46,@DTSStartWeek) WHEN 4 THEN dateadd(hour,-70,@DTSStartWeek) WHEN 5 THEN dateadd(hour,-94,@DTSStartWeek) WHEN 6 THEN dateadd(hour,-118,@DTSStartWeek) WHEN 7 THEN dateadd(hour,-142,@DTSStartWeek) END END
-- Daylight Savings did not exist. Return the largest smalldatetime. RETURN CAST('2079-06-06 23:59:00.000' AS smalldatetime); END |
|
| Wednesday, March 14, 2012 - 2:55:03 PM - cinnamongirl | Read The Tip |
|
Hello Tim, Thank you for the very detailed and informative article. I have copied your function as is but I donot want the granularity to hour precision. So, I have replaced it with day and when I execute the start date function, the result is NULL.Here is what I have changed. begin SELECT [Warehouse].[dbo].[fn_GetDaylightSavingsTimeStart] ( /* NULL */ Thanks Cinnamongirl |
|
| Thursday, March 15, 2012 - 7:49:49 PM - adkeller | Read The Tip |
|
Cinnamongirl, You should check the variables in your code. Replace all references to @DTSEndWeek with @DTSStartWeek, and remove the declaration of @DTSEndWeek. Tim's fn_GetDaylightSavingsTimeStart function is a little confusing because it declares @DTSEndWeek without using it. Hope this helps! |
|
| Thursday, March 15, 2012 - 10:54:11 PM - Tim Cullen | Read The Tip |
|
You're right, ADKeller-I think I was half asleep when I finished the script and didn't remove the @DTSEndWeek variable. Here is the one I just executed and it works:
CREATE function [dbo].[fn_GetDaylightSavingsTimeStart]
(@Year CHAR(4))
RETURNS smalldatetime
as
begin
declare @DTSStartWeek smalldatetime
set @DTSStartWeek = '03/01/' + @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
|
|
| Wednesday, March 06, 2013 - 10:14:49 PM - Beej | Read The Tip |
-- Here's another slightly disfigured version... this format is ready to create as a view or inline function SELECT DATEADD(HOUR, (MULTIPLIER + 7 /*skip the first week*/) * 24 /*24 hours a day*/ + 2 /*offset to 2am*/, StartWeek) AS StartDate, DATEADD(HOUR, MULTIPLIER * 24 + 2, EndWeek) AS EndDate FROM ( SELECT 0 AS MULTIPLIER UNION ALL SELECT 6 UNION ALL SELECT 5 UNION ALL SELECT 4 UNION ALL SELECT 3 UNION ALL SELECT 2 UNION ALL SELECT 1 ) dow, (SELECT '03/01/' + convert(varchar, DATEPART(YEAR, GETDATE())) AS StartWeek, '11/01/' + convert(varchar, DATEPART(YEAR, GETDATE())) AS EndWeek ) DST WHERE DATEPART(WEEKDAY, StartWeek) = (8 - MULTIPLIER) |
|
| Wednesday, March 06, 2013 - 10:31:01 PM - Beej | Read The Tip |
|
-- hey neat, no edit function combined with my lack of testing yierlded an immediate offspring SELECT DATEADD(HOUR, (MULTIPLIER + 7 /*skip the first week*/) * 24 /*24 hours a day*/ + 2 /*offset to 2am*/, StartWeek) AS StartDate, |
|
|
privacy | disclaimer | copyright | advertise | about authors | contribute | feedback | giveaways | user groups Some names and products listed are the registered trademarks of their respective owners. Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com |