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
