Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Daylight Savings Time Functions in SQL Server

MSSQLTips author Tim Cullen By:   |   Read Comments (8)   |   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

 



Last Update: 11/13/2007


About the author
MSSQLTips author Tim Cullen
Tim Cullen has been working in the IT industry since 2003 and currently works as a SQL Server Reports Developer.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
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
declare @DTSStartWeek smalldatetime, @DTSEndWeek smalldatetime
set @DTSStartWeek = '03/01/' + convert(varchar,@Year)
return case datepart(dw,@DTSStartWeek)
when 1 then
dateadd(day,1,@DTSEndWeek)
when 2 then
dateadd(day,2,@DTSEndWeek)
when 3 then
dateadd(day,3,@DTSEndWeek)
when 4 then
dateadd(day,4,@DTSEndWeek)
when 5 then
dateadd(day,5,@DTSEndWeek)
when 6 then
dateadd(day,6,@DTSEndWeek)
when 7 then
dateadd(day,7,@DTSEndWeek)
end
end

SELECT [Warehouse].[dbo].[fn_GetDaylightSavingsTimeStart] (
   2012)
GO

/*

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, 
  DATEADD(HOUR, MULTIPLIER * 24 + 2, EndWeek) AS EndDate 
FROM ( 
  SELECT 0 AS MULTIPLIER, 1 AS DOW UNION ALL 
  SELECT 6, 2 UNION ALL 
  SELECT 5, 3 UNION ALL 
  SELECT 4, 4 UNION ALL 
  SELECT 3, 5 UNION ALL 
  SELECT 2, 6 UNION ALL 
  SELECT 1, 7 
) 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) = DOW


Wednesday, May 29, 2013 - 3:01:39 PM - Paul Simpson Read The Tip

Thanks, Tim!  I have encapsulated your "Sample Execution" part into a function, too, and have included comments for how it may be used by a stored proc that calls it. Note that I have changed the names of your functions slightly, so that all three functions will begin the same way, and thus be grouped together in the list in SQL Server Management Studio:

 -- =============================================

 

-- Author:   based on work of Tim Cullen

 

-- http://www.mssqltips.com/sqlservertip/1372/daylight-savings-time-functions-in-sql-server/

 

-- Description: Returns a 1 or 0 for whether Daylight Saving Time is in effect for the date time passed in.

 

-- NOTE: The other functions called here are specific to the time of day. That is, if it is Sunday on the

 

-- first day of DST, but not yet 2 am (such as passing in the date at midnight), the returned result will

 

-- be 0, not the 1 you may have expected. So, pass in the date AND time if you want to be very specific.

 

-- Example usage: from a stored proc, you may have a table with a datetime field called TimeUtc,

 

-- which is the universal time (aka Greenwich Mean Time or Zulu Time).

 

-- If you want to represent that time in our current central time,

 

-- taking into account daylight saving time, you could do this:

 

--

 

-- CASE Support.dbo.udf_DaylightSavingTime_IsInEffect(TimeUtc)

 

-- WHEN 1 THEN DATEADD(hour, - 5, TimeUtc) -- because UTC doesn't observe DST.

 

-- ELSE DATEADD(hour, - 6, TimeUtc) -- The normal hours difference between UTC and CST

 

-- END AS CentralTime

 

--

 

-- =============================================

 

ALTERfunction [dbo].[udf_DaylightSavingTime_IsInEffect]

(@DtTime smalldatetime)

RETURNStinyint

 

AS

 

BEGIN

 

 

 

DECLARE @DLSStart smalldatetime

, @DLSEnd smalldatetime

, @DLSActive tinyint

SET @DLSStart =(SELECT dbo.udf_DaylightSavingTime_GetStart(CONVERT(varchar,DATEPART(YEAR,@DtTime))))

SET @DLSEnd =(SELECT dbo.udf_DaylightSavingTime_GetEnd(CONVERT(varchar,DATEPART(YEAR,@DtTime))))

 

IF @DtTime BETWEEN @DLSStart AND @DLSEnd

SET @DLSActive = 1

ELSE

SET @DLSActive = 0

 

RETURN @DLSActive

END

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.