Free SQL Server Learning

# Daylight Savings Time Functions in SQL Server

 By: Tim Cullen   |   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

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:

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

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

View all my tips
Related Resources

 Print Tweet Become a paid author

All comments are reviewed, so stay on subject or we may delete your comment.

Get free SQL tips:

 *Enter Code

 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

 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, 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)` ` `

 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

 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!

 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 thendateadd(day,1,@DTSEndWeek)when 2 thendateadd(day,2,@DTSEndWeek)when 3 thendateadd(day,3,@DTSEndWeek)when 4 thendateadd(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

 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.

## Follow

Get Free SQL Tips

Pinterest

DBAs

Developers

BI Professionals

Careers

Q and A

Today's Tip

Tutorials

Webcasts

Whitepapers

Tools

Tip Categories

Search By TipID

Top Ten

Authors

## Community

First Timer?

Pictures

Free T-shirt

Contribute

Events

User Groups

Author of the Year

Join