This procedure uses the sDayOfMonth procedure, to get the calendar day, then checks if that day is subject to daylight saving time.
This procedure is only accurate to the "day" and not the *hours* prior to the change at 2am (which was just fine for my application).
CREATE PROCEDURE sCheckForDaylightSavingTime
(
@date datetime,
@daylightsaving bit OUTPUT
)
/*
***********************************************************
*** SP Name: sCheckForDaylightSavingTime
*** Date Created: 02/12/2004
***********************************************************
***
*** Description:
***
*** This procedure will return if a given day is subject to daylight saving time, per Public Law 99-359.
*** Daylight Saving Time is in effect from the first sunday in April to the Last Sunday in October.
*** This procedure is only accurate to a "day" and not the hours from midnight to 2am (prior to the change at 2am),
*** when switching between daylight and standard time.
***
*** Output:
*** 1 = daylight saving, 0 = standard time
***
***********************************************************
*/
AS
declare @year INT
set @year = datepart ( yyyy, @date )
declare @month INT
set @month = datepart ( mm, @date )
declare @day INT
set @day = datepart ( dd , @date )
if ( @month < 4 or @month > 10 )
begin
-- early / late in the year, standard time
select @daylightsaving = 0
return
end
if ( @month > 4 and @month < 10 )
begin
-- middle of the year, daylight saving time
select @daylightsaving = 1
return
end
declare @dayofmonth int
declare @dayofmonth2 int
-- check the day in april, days prior to 1st sunday are standard time.
if ( @month = 4 )
begin
-- get the 1st sunday of the month
execute sDayOfMonth @dayofmonth OUTPUT, @year, 4, 1, 0
-- check if our day is prior to that date
if ( @day < @dayofmonth )
begin
-- standard time
select @daylightsaving = 0
return
end
-- otherwise it's daylight saving time.
select @daylightsaving = 1
return
end
-- check for the last sunday day in october
-- the last sunday in oct, could be 4th or 5th week.
if (@month = 10)
begin
execute sDayOfMonth @dayofmonth OUTPUT, @year, 10, 4, 0
execute sDayOfMonth @dayofmonth2 OUTPUT, @year, 10, 5, 0
-- if 5th week, just copy that value into @dayofmonth, and continue
if ( @dayofmonth2 < 32)
begin
set @dayofmonth = @dayofmonth2
end
-- check if our day is on, or after that sunday.
if ( @day >= @dayofmonth )
begin
-- standard time
select @daylightsaving = 0
return
end
-- daylight saving time
select @daylightsaving = 1
return
end
GO