Daylight Savings Time in SQL Server

We're running a vendor system that stores every date/time object in GMT. This isn't a bad thing, but it does require converting GMT -> Local time in every place we used it: select statements, where clauses, corelated subqueries, etc. To do this we started out years ago with a simple Function plus a database table to store DST dates.

That function looked like this:

CREATE FUNCTION [dbo].[ConvertGMT]

(@MYDATE DATETIME)

RETURNS DATETIME AS

BEGIN

DECLARE @OFFSET INT

DECLARE @YEAR INT

DECLARE @DST_BEGIN DATETIME

DECLARE @DST_END DATETIME

DECLARE @RETURNED_DATE DATETIME

SET @YEAR = YEAR(@MYDATE)

SET @DST_BEGIN = (select DST_BEGIN from dbo.DST_LOOKUP where YEAR_LOOKUP = @YEAR)

SET @DST_END = (select DST_END from dbo.DST_LOOKUP where YEAR_LOOKUP = @YEAR)

IF (@MYDATE BETWEEN @DST_BEGIN AND @DST_END)

SET @OFFSET = -4

ELSE

SET @OFFSET = -5

SET @RETURNED_DATE = DATEADD(hour,@OFFSET,@MYDATE)

RETURN @RETURNED_DATE

END

and the associated table looked like this:

DST_BEGIN DST_END YEAR_LOOKUP

4/4/1999 2:00:00 AM 10/31/1999 3:00:00 AM 1999

4/2/2000 2:00:00 AM 10/29/2000 3:00:00 AM 2000

4/1/2001 2:00:00 AM 10/28/2001 3:00:00 AM 2001

4/7/2002 2:00:00 AM 10/27/2002 3:00:00 AM 2002

4/6/2003 2:00:00 AM 10/26/2003 3:00:00 AM 2003

4/4/2004 2:00:00 AM 10/31/2004 3:00:00 AM 2004

4/3/2005 2:00:00 AM 10/30/2005 3:00:00 AM 2005

4/2/2006 2:00:00 AM 10/29/2006 3:00:00 AM 2006

3/11/2007 2:00:00 AM 11/4/2007 3:00:00 AM 2007

3/9/2008 2:00:00 AM 11/2/2008 3:00:00 AM 2008

3/8/2009 2:00:00 AM 11/1/2009 3:00:00 AM 2009

3/14/2010 2:00:00 AM 11/7/2010 3:00:00 AM 2010

3/13/2011 2:00:00 AM 11/6/2011 3:00:00 AM 2011

This worked well enough for a while, but it cost a couple of table hits for every conversion. That adds up when you're doing 6 conversion per row returning a couple hundred rows a few times a minute. This benchmarked at ≅15seconds to return 45,000 at 3 conversions / row in a simple select.

Our next step was to eliminate the table lookup. That was simple and inelegant, and looked like this:

ALTER FUNCTION [dbo].[ConvertGMT] (@MYDATE DATETIME)

RETURNS DATETIME AS

BEGIN

DECLARE @OFFSET INT

DECLARE @YEAR INT

DECLARE @RETURNED_DATE DATETIME

SET @YEAR = YEAR(@MYDATE)

IF @YEAR = 2010 AND @MYDATE between 'Mar 14 2010 2:00AM ' and 'Nov 7 2010 3:00AM ' SET @OFFSET = -4

ELSE IF @YEAR = 2009 AND @MYDATE between 'Mar 8 2009 2:00AM ' and 'Nov 1 2009 3:00AM ' SET @OFFSET = -4

ELSE IF @YEAR = 2008 AND @MYDATE between 'Mar 9 2008 2:00AM ' and 'Nov 2 2008 3:00AM ' SET @OFFSET = -4

ELSE IF @YEAR = 2007 AND @MYDATE between 'Mar 11 2007 2:00AM ' and 'Nov 4 2007 3:00AM ' SET @OFFSET = -4

ELSE IF @YEAR = 2006 AND @MYDATE between 'Apr 2 2006 2:00AM ' and 'Oct 29 2006 3:00AM ' SET @OFFSET = -4

ELSE IF @YEAR = 2005 AND @MYDATE between 'Apr 3 2005 2:00AM ' and 'Oct 30 2005 3:00AM ' SET @OFFSET = -4

ELSE IF @YEAR = 2004 AND @MYDATE between 'Apr 4 2004 2:00AM ' and 'Oct 31 2004 3:00AM ' SET @OFFSET = -4

ELSE IF @YEAR = 2003 AND @MYDATE between 'Apr 6 2003 2:00AM ' and 'Oct 26 2003 3:00AM ' SET @OFFSET = -4

ELSE IF @YEAR = 2002 AND @MYDATE between 'Apr 7 2002 2:00AM ' and 'Oct 27 2002 3:00AM ' SET @OFFSET = -4

ELSE IF @YEAR = 2001 AND @MYDATE between 'Apr 1 2001 2:00AM ' and 'Oct 28 2001 3:00AM ' SET @OFFSET = -4

ELSE IF @YEAR = 2000 AND @MYDATE between 'Apr 2 2000 2:00AM ' and 'Oct 29 2000 3:00AM ' SET @OFFSET = -4

ELSE IF @YEAR = 1999 AND @MYDATE between 'Apr 4 1999 2:00AM ' and 'Oct 31 1999 3:00AM ' SET @OFFSET = -4

ELSE IF @YEAR = 2011 AND @MYDATE between 'Mar 13 2011 2:00AM ' and 'Nov 6 2011 3:00AM ' SET @OFFSET = -4

ELSE IF @YEAR = 2012 AND @MYDATE between 'Mar 11 2012 2:00AM ' and 'Nov 4 2012 3:00AM ' SET @OFFSET = -4

ELSE IF @YEAR = 2013 AND @MYDATE between 'Mar 10 2013 2:00AM ' and 'Nov 3 2013 3:00AM ' SET @OFFSET = -4

ELSE IF @YEAR = 2014 AND @MYDATE between 'Mar 9 2014 2:00AM ' and 'Nov 2 2014 3:00AM ' SET @OFFSET = -4

ELSE SET @OFFSET = -5

SET @RETURNED_DATE = DATEADD(hour,@OFFSET,@MYDATE)

RETURN @RETURNED_DATE

END

The performance boost with this version was amazing. It processed the same 45,000 x 3 conversion / row query in ≅3 seconds. Across all our GUI and report calls, that was a noticeable performance boost. But, it looked like something someone in CS101 would write, and it still required occasional updates to add new years and keep current.

The final step - and current solution - started with a DST discussion at MSSqlTips that used some pre-calculated offsets to dynamically calculate DST start and stop dates based on the DOW of the start and end months. This worked well for dates 2007 and later, but earlier dates had a different date pattern. I continued the same model that Tim Cullen started with the MSSqlTips post and used a set of precalculated offsets for 2006 and earlier starts, and dynamically calculated DST end dates.

The final function looks like this:

CREATE FUNCTION [dbo].[ConvertGMT]

(@MYDATE DATETIME)

RETURNS DATETIME AS

BEGIN

DECLARE @OFFSET INT

DECLARE @YEAR INT

DECLARE @DST_BEGIN DATETIME

DECLARE @DST_END DATETIME

DECLARE @RETURNED_DATE DATETIME

SET @YEAR = YEAR(@MYDATE)

declare @DSTStartWeek smalldatetime, @DSTEndWeek smalldatetime

if @YEAR >= 2007

BEGIN

set @DSTStartWeek = '03/01/' + convert(varchar,@YEAR)

SET @DST_BEGIN = case datepart(dw,@DSTStartWeek)

when 1 then

dateadd(hour,170,@DSTStartWeek)

when 2 then

dateadd(hour,314,@DSTStartWeek)

when 3 then

dateadd(hour,290,@DSTStartWeek)

when 4 then

dateadd(hour,266,@DSTStartWeek)

when 5 then

dateadd(hour,242,@DSTStartWeek)

when 6 then

dateadd(hour,218,@DSTStartWeek)

when 7 then

dateadd(hour,194,@DSTStartWeek)

end

set @DSTEndWeek = '11/01/' + convert(varchar,@Year)

SET @DST_END = case datepart(dw,dateadd(week,1,@DSTEndWeek))

when 1 then

dateadd(hour,2,@DSTEndWeek)

when 2 then

dateadd(hour,146,@DSTEndWeek)

when 3 then

dateadd(hour,122,@DSTEndWeek)

when 4 then

dateadd(hour,98,@DSTEndWeek)

when 5 then

dateadd(hour,74,@DSTEndWeek)

when 6 then

dateadd(hour,50,@DSTEndWeek)

when 7 then

dateadd(hour,26,@DSTEndWeek)

end

END

ELSE

BEGIN

set @DSTStartWeek = '04/01/' + convert(varchar,@YEAR)

SET @DST_BEGIN = case datepart(dw,@DSTStartWeek)

when 1 then

dateadd(hour,2,@DSTStartWeek)

when 2 then

dateadd(hour,146,@DSTStartWeek)

when 3 then

dateadd(hour,122,@DSTStartWeek)

when 4 then

dateadd(hour,98,@DSTStartWeek)

when 5 then

dateadd(hour,74,@DSTStartWeek)

when 6 then

dateadd(hour,50,@DSTStartWeek)

when 7 then

dateadd(hour,26,@DSTStartWeek)

end

SET @DSTEndWeek = '11/01/' + convert(varchar,@Year)

SET @DST_END = dateadd(hh,3,dateadd (dd,(1 - datepart(dw,@DSTEndWeek)),@DSTEndWeek))

END

IF @MYDATE between @DST_BEGIN and @DST_END

SET @OFFSET = -4

ELSE

SET @OFFSET = -5

SET @RETURNED_DATE = DATEADD(hour,@OFFSET,@MYDATE)

RETURN @RETURNED_DATE

END

This code has one change from Tim's code - it now ends at 3am in November, for 2007 and later. This is what we have in production now. It's just as performant as the previous version (≅3 sec for the benchmark query), but it has the advantage of never needing updating for new years. It's more elegant than it's predecessors, but I have no doubt that it could be done better.

Use, share, comment.

-Bill

(Thanks to the Wikipedia DST Article for clarity on this.)