IsDST function for SQL Server


I've seen my share of in-house logics to detect if a given day falls under daylight saving time or not, and the conclusion I've come to is this; (A) if you can get away without having to use IsDST() function, that probably is a better way to go, but (B) if you must, don't compute, use lookup.

Without further ado, here is our standard IsDST function that we can't tune any faster.

CREATE function [dbo].[IsDST] (@localTime datetime)

returns bit

as

/*

SATO Database Architects LLC

Pensacola, FL USA - (844)-SQL-FAST

www.satodata.com

This function returns 1 if @locaTime given is a US daylight saving time, 0 if not.

*/

begin

declare @result bit = 0

declare @DstStSchedule table

(

CalYear int PRIMARY KEY NOT NULL,

DstBeginsLocal datetime NOT NULL,

DstEndsLocal datetime NOT NULL

)

insert into @DstStSchedule

values

(2001,'2001-04-01 2AM','2001-10-28 2AM'),

(2002,'2002-04-07 2AM','2002-10-27 2AM'),

(2003,'2003-04-06 2AM','2003-10-26 2AM'),

(2004,'2004-04-04 2AM','2004-10-31 2AM'),

(2005,'2005-04-03 2AM','2005-10-30 2AM'),

(2006,'2006-04-02 2AM','2006-10-29 2AM'),

(2007,'2007-03-11 2AM','2007-11-04 2AM'),

(2008,'2008-03-09 2AM','2008-03-30 2AM'),

(2009,'2009-03-08 2AM','2009-11-01 2AM'),

(2010,'2010-03-14 2AM','2010-11-07 2AM'),

(2011,'2011-03-13 2AM','2011-11-06 2AM'),

(2012,'2012-03-11 2AM','2012-11-04 2AM'),

(2013,'2013-03-10 2AM','2013-11-03 2AM'),

(2014,'2014-03-09 2AM','2014-11-02 2AM'),

(2015,'2015-03-08 2AM','2015-11-01 2AM'),

(2016,'2016-03-13 2AM','2016-11-06 2AM'),

(2017,'2017-03-12 2AM','2017-11-05 2AM'),

(2018,'2018-03-11 2AM','2018-11-04 2AM'),

(2019,'2019-03-10 2AM','2019-11-03 2AM'),

(2020,'2020-03-08 2AM','2020-11-01 2AM'),

(2021,'2021-03-14 2AM','2021-11-07 2AM'),

(2022,'2022-03-13 2AM','2022-11-06 2AM'),

(2023,'2023-03-12 2AM','2023-11-05 2AM'),

(2024,'2024-03-10 2AM','2024-11-03 2AM'),

(2025,'2025-03-09 2AM','2025-11-02 2AM'),

(2026,'2026-03-08 2AM','2026-11-01 2AM'),

(2027,'2027-03-14 2AM','2027-11-07 2AM'),

(2028,'2028-03-12 2AM','2028-11-05 2AM'),

(2029,'2029-03-11 2AM','2029-11-04 2AM'),

(2030,'2030-03-10 2AM','2030-11-03 2AM'),

(2031,'2031-03-09 2AM','2031-11-02 2AM'),

(2032,'2032-03-14 2AM','2032-11-07 2AM'),

(2033,'2033-03-13 2AM','2033-11-06 2AM')

select @result = CASE WHEN @localTime between DstBeginsLocal and DstEndsLocal THEN 1 ELSE 0 END

from @DstStSchedule where CalYear = DATEPART(YEAR,@localTime)

return @result

end

#IsDST #DaylightSavingTIme #SQLServer #TSQL #DST #function #CLR

Featured Posts
Recent Posts
Archive
Search By Tags
No tags yet.
Follow Us
  • Facebook Basic Square
  • Twitter Basic Square
  • Google+ Basic Square
  • facebook
  • Twitter Round
  • googleplus
  • flickr

© 2014 SATO Database Architects, LLC. SQL Server, SQL Azure are registered trademarks of Microsoft Corporation.