# Daylight Savings Time Functions in SQL Server

By:   |   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)
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)
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

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

View all my tips

 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 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