How to Create Daylight Savings Time Start and End Function in SQL Server

How to create Daylight Savings time Start and End function in SQL Server

As pointed out in comments, right now (March 2022) this calculation looks likely to change next year: US may not switch off of DST in the fall.

Don't forget that daylight saving time schedules change depending on country, and also are subject to change over the years: the current (as of 2013 through 2022) US system took effect in 2007, for example.

Assuming you want the current system for the US, here's one form of an answer for any given year.

SET DATEFIRST 7

DECLARE @year INT = 2013
DECLARE
    @StartOfMarch DATETIME ,
    @StartOfNovember DATETIME ,
    @DstStart DATETIME ,
    @DstEnd DATETIME

  
SET @StartOfMarch = DATEADD(MONTH, 2, DATEADD(YEAR, @year - 1900, 0))
SET @StartOfNovember = DATEADD(MONTH, 10, DATEADD(YEAR, @year - 1900, 0));
SET @DstStart = DATEADD(HOUR, 2,
                        DATEADD(day,
                                ( ( 15 - DATEPART(dw, @StartOfMarch) ) % 7 )
                                + 7, @StartOfMarch))
SET @DstEnd = DATEADD(HOUR, 2,
                      DATEADD(day,
                              ( ( 8 - DATEPART(dw, @StartOfNovember) ) % 7 ),
                              @StartOfNovember))

SELECT
    @DstStart AS DstStartInUS ,
    @DstEnd AS DstEndInUS

or as functions, but you have to know that DateFirst is set to 7, otherwise the math will be off.

CREATE FUNCTION GetDstStart ( @Year AS INT )
RETURNS DATETIME
AS
    BEGIN

        DECLARE
            @StartOfMarch DATETIME ,
            @DstStart DATETIME 

        SET @StartOfMarch = DATEADD(MONTH, 2,
                                    DATEADD(YEAR, @year - 1900, 0))
        SET @DstStart = DATEADD(HOUR, 2,
                                DATEADD(day,
                                        ( ( 15 - DATEPART(dw,
                                                          @StartOfMarch) )
                                          % 7 ) + 7, @StartOfMarch))
        RETURN @DstStart
    END

GO;

CREATE FUNCTION GetDstEnd ( @Year AS INT )
RETURNS DATETIME
AS
    BEGIN
        DECLARE
            @StartOfNovember DATETIME ,
            @DstEnd DATETIME

        SET @StartOfNovember = DATEADD(MONTH, 10,
                                       DATEADD(YEAR, @year - 1900, 0))
        SET @DstEnd = DATEADD(HOUR, 2,
                              DATEADD(day,
                                      ( ( 8 - DATEPART(dw,
                                                       @StartOfNovember) )
                                        % 7 ), @StartOfNovember))
        RETURN @DstEnd
    END

Daylight savings time in Sql Server

Instead of 3600, you'll want to do DateDiff(s, getutcdate(), getdate())+unix_time, which will give you the correct offset from the UTC time.

Cheers,

Eric

Handle Daylight Saving Time on SQL Server

Handling Daylight time 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

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

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 

SQL Calculate duration of time in hours crossing DST boundaries

Because of the uncertainty you've identified in DST schedules at different locations, it is often recommended to do all datetime calculations in UTC and display them in local time for clients. DST rules can change at any time based on local laws, even from city to city that UTC is by far the best way to calculate time.

Edit for additional clarity: Even the SQL 2016 fix will rely on human intervention and rule updates to be keep current as different countries, states, and cities make changes to their DST laws. UTC is the best consistently reliable tool at your disposal.

---

Related Topics