Daylight Savings Time Functions in SQL Server

By:   |   Updated: 2007-11-13   |   Comments (8)   |   Related: > 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.

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2007-11-13

Comments For This Article




Wednesday, May 29, 2013 - 3:01:39 PM - Paul Simpson Back To Top (25189)

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Wednesday, March 6, 2013 - 10:31:01 PM - Beej Back To Top (22615)

-- 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, March 6, 2013 - 10:14:49 PM - Beej Back To Top (22614) 
-- 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)
 

Thursday, March 15, 2012 - 10:54:11 PM - Tim Cullen Back To Top (16420)
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

Thursday, March 15, 2012 - 7:49:49 PM - adkeller Back To Top (16417)

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!

Wednesday, March 14, 2012 - 2:55:03 PM - cinnamongirl Back To Top (16400)

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, December 8, 2011 - 9:22:21 AM - adkeller Back To Top (15317)

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

Monday, June 15, 2009 - 6:05:11 AM - jcelko Back To Top (3553)

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.

 

 














get free sql tips
agree to terms