I came across some dialog where adjustment to daylight savings time was being applied manually in SQL Server stored procedures or functions to calculate the appropriate timezone shift (e.g. sometimes -5 for EST and sometimes -4). I figured it can be done automatically, so I did some research on the definition for Day Light Savings time and came across two rules:
The old rule states:
Starts on the first Sunday of April at 2am and ends on the Last Sunday of October at 2am.
Comparing the results I realized something was off when compared to my current computer clock so I rechecked and found the revised rule enacted a few years ago:
The new rule states:
Starts on the second Sunday of March at 2am and ends on the First Sunday of November at 2am.
I decided to write some quick T-SQL to compute this value automatically based on these rules, and to make the effort worth while, publishing it as a blog for future reference and public consumption. By a variation of the pigeon hole principle, the start date for computing the second Sunday of March is 3/8 of the current year.
The code is self documenting. To minimize word wrapping, I made the font a bit small but it can copied and pasted. Enjoy
declare @currentYear int=datepart(year,getdate())
declare @secondSundayOfMar datetime = CAST('3/8/' + CAST(@currentYear as varchar) as datetime)
declare @firstSundayOfNov datetime = CAST( '11/1/' + CAST(@currentYear as varchar) as datetime)
--find first sunday
while( DATENAME(WEEKDAY,@secondSundayOfMar) != 'Sunday' )
set @secondSundayOfMar = DATEADD(day,1,@secondSundayOfMar)
--find last sunday of october
while( DATENAME(WEEKDAY,@firstSundayOfNov) != 'Sunday' )
set @firstSundayOfNov = DATEADD(day,-1,@firstSundayOfNov)
declare @gmt int= 0
declare @currentDate datetime = getDate()
if ( @currentDate >= @secondSundayOfMar AND @currentDate < @firstSundayOfNov )
set @gmt = -4
set @gmt = -5