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

Raffo

I recommend that when you wake up on Sunday, you run a select getdate() query. If it returns the correct time for where you live, it might not be necessary to do anything. – Northnorthwest

Thank you, I should clarify...the date that is stored in our database is UTC time and is captured at the time of each transaction. We only collect data from the US, but in different timezones. I need to convert the dates to EST time for reporting purposes. So when I run the query I have been manually calculating the est time. However, since dst is ending this Sunday and I have a ton of reports to modify I was hoping to create a function so I won't have to do this again in March :) – Raffo

Answer 1

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

Silverplate

Thank you, I would like to create the functions because I use Excel to retrieve the data for the pivot reports and I can't Declare variables when I'm using the Microsfot Query function for some reason...When you mention that "you have to know that DateFirst is set to 7, otherwise the math will be off." where would that be assigned in the function? Or do I just need to know what for general knowledge when doing calculations in this way? I've never use the DATEFIRST function before. :) – Raffo

DATEFIRST is a SQL server setting that determines what the server considers the first day of a week. US, and SQL Server default, is 7, or Sunday, but 1 (Monday) is sometimes used. You can't change this in a function, but you can for a stored procedure. @@DATEFIRST will retrieve the value. technet.microsoft.com/en-us/library/ms181598.aspx – Silverplate

Awesome, it works perfectly! I spent some time trying to understand the math behind it and I came to the conclusion that you don't need to subtract the day of the week (dw) from 15, you can use 8, because you are then %7, right? Not pretending to outsmart you or anything, just making sure I got it right! TY – Kirit

@Kirit Yes, you are exactly right. I don't recall the reasoning for the 15 versus 8, but the result is the same. – Silverplate

Hi. Just to note, these rules for DST might soon be changing. If you're using this function, you'll need to update it if this law is enacted. Preferably, one should not rely on hard-coded rules like that. Modern SQL Server can use AT TIME ZONE instead. Thanks. – Irrigate

Answer 2

Personally, I think it's easier to find the first Sunday in November than it is to find the second Sunday in March. Luckily, if you find one, you can find the other because there's always 238 days between them. So here's a handy function to find the end of Dst:

create function GetDstEnd (
                           @Year int
                          )
returns datetime
as
begin

   declare @DstEnd datetime;

   ;with FirstWeekOfNovember
   as (
       select top(7)
              cast(@Year as char(4))
            + '-11-0'
            + cast(row_number() over(order by object_id) as char(1))
            + ' 02:00:00'
              'DST_Stops'
         from sys.columns
      )
   select @DstEnd = DST_Stops
     from FirstWeekOfNovember
    where datepart(weekday,DST_Stops) = 1

   return @DstEnd;

end;

Now the Start of Dst is the same function, only 238 days earlier.

create function GetDstStart (
                             @Year int
                            )
returns datetime
as
begin;

   declare @DstStart datetime;

   ;with FirstWeekOfNovember
   as (
       select top(7)
              cast(@Year as char(4))
            + '-11-0'
            + cast(row_number() over(order by object_id) as char(1))
            + ' 02:00:00'
              'DST_Stops'
         from sys.columns
      )
   select @DstStart = dateadd(day,-238,DST_Stops)
     from FirstWeekOfNovember
    where datepart(weekday,DST_Stops) = 1

   return @DstStart;

end;
go

Thapsus

shouldn't you find March and goto November? Going from November to March, introduces the LeapYear calculation. Although, not that difficult, adds more complexity than is needed. – Longinus

I think you misunderstand, @Longinus - There are always 238 days between the 2nd Sunday in March and the 1st Sunday in November for any given year. Leap year is before March, so it doesn't matter if there are 28 or 29 days in it. I find it's easier to get the 1st Sunday in November then subtract 238 days than it is to find the 2nd Sunday in March and add 238 days. – Thapsus

Exactly...your statement I think it's easier to find the first Sunday in November than it is to find the second Sunday in March...which i interpreted as being start with November and goto March...but then you self-correct with ...because there's always 238 days between them..., which is also confusing as you do not determine which date to start with and which to end. Although assumption would be that March to November would be the bigger window....I find it best to not assume with SO community ;) – Longinus

Hi. Just to note, these rules for DST might soon be changing. If you're using this function, you'll need to update it if this law is enacted. Preferably, one should not rely on hard-coded rules like that. Modern SQL Server can use AT TIME ZONE instead. Thanks. – Irrigate

I would LOVE the burden of removing DTS transformations!!! – Thapsus

Answer 3

SQL Server version 2016 will solve this issue once and for all. For earlier versions a CLR solution is probably easiest. Or for a specific DST rule (like US only), a T-SQL function can be relatively simple.

However, I think a generic T-SQL solution might be possible. As long as xp_regread works, try this:

CREATE TABLE #tztable (Value varchar(50), Data binary(56));
DECLARE @tzname varchar(150) = 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation'
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', @tzname, 'TimeZoneKeyName', @tzname OUT;
SELECT @tzname = 'SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones\' + @tzname
INSERT INTO #tztable
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', @tzname, 'TZI';
SELECT                                                                                  -- See http://msdn.microsoft.com/ms725481
 CAST(CAST(REVERSE(SUBSTRING(Data,  1, 4)) AS binary(4))      AS int) AS BiasMinutes,   -- UTC = local + bias: > 0 in US, < 0 in Europe!
 CAST(CAST(REVERSE(SUBSTRING(Data,  5, 4)) AS binary(4))      AS int) AS ExtraBias_Std, --   0 for most timezones
 CAST(CAST(REVERSE(SUBSTRING(Data,  9, 4)) AS binary(4))      AS int) AS ExtraBias_DST, -- -60 for most timezones: DST makes UTC 1 hour earlier
 -- When DST ends:
 CAST(CAST(REVERSE(SUBSTRING(Data, 13, 2)) AS binary(2)) AS smallint) AS StdYear,       -- 0 = yearly (else once)
 CAST(CAST(REVERSE(SUBSTRING(Data, 15, 2)) AS binary(2)) AS smallint) AS StdMonth,      -- 0 = no DST
 CAST(CAST(REVERSE(SUBSTRING(Data, 17, 2)) AS binary(2)) AS smallint) AS StdDayOfWeek,  -- 0 = Sunday to 6 = Saturday
 CAST(CAST(REVERSE(SUBSTRING(Data, 19, 2)) AS binary(2)) AS smallint) AS StdWeek,       -- 1 to 4, or 5 = last <DayOfWeek> of <Month>
 CAST(CAST(REVERSE(SUBSTRING(Data, 21, 2)) AS binary(2)) AS smallint) AS StdHour,       -- Local time
 CAST(CAST(REVERSE(SUBSTRING(Data, 23, 2)) AS binary(2)) AS smallint) AS StdMinute,
 CAST(CAST(REVERSE(SUBSTRING(Data, 25, 2)) AS binary(2)) AS smallint) AS StdSecond,
 CAST(CAST(REVERSE(SUBSTRING(Data, 27, 2)) AS binary(2)) AS smallint) AS StdMillisec,
 -- When DST starts:
 CAST(CAST(REVERSE(SUBSTRING(Data, 29, 2)) AS binary(2)) AS smallint) AS DSTYear,       -- See above
 CAST(CAST(REVERSE(SUBSTRING(Data, 31, 2)) AS binary(2)) AS smallint) AS DSTMonth,
 CAST(CAST(REVERSE(SUBSTRING(Data, 33, 2)) AS binary(2)) AS smallint) AS DSTDayOfWeek,
 CAST(CAST(REVERSE(SUBSTRING(Data, 35, 2)) AS binary(2)) AS smallint) AS DSTWeek,
 CAST(CAST(REVERSE(SUBSTRING(Data, 37, 2)) AS binary(2)) AS smallint) AS DSTHour,
 CAST(CAST(REVERSE(SUBSTRING(Data, 39, 2)) AS binary(2)) AS smallint) AS DSTMinute,
 CAST(CAST(REVERSE(SUBSTRING(Data, 41, 2)) AS binary(2)) AS smallint) AS DSTSecond,
 CAST(CAST(REVERSE(SUBSTRING(Data, 43, 2)) AS binary(2)) AS smallint) AS DSTMillisec
FROM #tztable;
DROP TABLE #tztable

A (complex) T-SQL function could use this data to determine the exact offset for all dates during the current DST rule.

Whirlpool

I try to stay away from CLR as much as possible...It is one more "setting" to validate before an in-place Upgrade or RC patch. Tentatively, the client back-burnered the need to have this in SQL but I have a feeling it will pop its "ugly" head up again, down the road. – Longinus

That first sentence! I cannot upvote this answer enough! I wish I had known about at time zone long ago! – Mounts

Answer 4

FWIW, there is much easier option starting with SQL Server 2016. System table 'sys.time_zone_info' has the offsets and DST flag to determine how to convert to the desired timezone. For more information about this table, please see the MS Doc.

You can identify the version of your SQL Server by this simple query: SELECT @@VERSION;

You can identify the timezone your SQL Server is in by this simple query: SELECT CURRENT_TIMEZONE();

You should be able to put together these resources to convert datetime values to approp. timezone while respecting the DST rules.

Camarena

SELECT CURRENT_TIMEZONE(); works on 2019 but not in 2017 for me. MS documentation claims it should work down to 2016 learn.microsoft.com/en-us/sql/t-sql/functions/… – Suspicious

Answer 5

I wasn't really satisfied with any of the solutions I found online to convert UTC to local time, so I came up with this function. Have a look at my SO answer here

There is some logic in there that calculates whether daylight savings is active based on the standard date range DST uses (Second Sunday in March at 2am, clocks move forward; 1st Sunday in November revert to standard time)

Morris