Michael Olivero
The official blog of Michael Olivero, Software Architect & Humble Entrepreneur

T-SQL Compute GMT Offset For Date Light Savings Time Automatically

Tuesday, 2 April 2013 18:01 by Michael Olivero

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


--for EST

if ( @currentDate >= @secondSundayOfMar AND @currentDate < @firstSundayOfNov )

set @gmt = -4


set @gmt = -5



print @gmt

Tags:   ,
Categories:   Software
Actions:   E-mail | del.icio.us | Permalink | Comments (0) | Comment RSSRSS comment feed