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

Question

I need to create a function in SQL server that returns daylight savings time start datetime and daylight savings time end datetime.

I've come across a few examples on the web, however they all are using the 1st date of March and the 1st date of November and thats not technically correct.

Daylight savings time begins at 2AM on the 2nd Sunday of March and ends on at 2AM in the first Sunday in November.

I've started with the below code but I'm sure its wrong. Any assistance is appreciated! :)

DECLARE @DSTSTART DATETIME

SELECT @DSTSTART = CASE WHEN 
DATEPART(MONTH, SYSDATETIME()) = 3
AND DATEPART(weekday, SYSDATETIME()) = 1
AND DATEDIFF(week,dateadd(week, datediff(week, 0, dateadd(month, datediff(month, 0, SYSDATETIME()), 0)), 0), SYSDATETIME() - 1) = 2
AND DATEPART(HOUR, SYSDATETIME()) = 2
THEN SYSDATETIME()
END
RETURN (@DSTSTART)
END
GO

Answer 1

Don't forget that daylight saving time schedules change depending on country, and also are subject to change over the years: the current 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