Daylight savings time

Does anyone have a good daylight savings time function? I need to get it going today, and am thinking of being lazy and just putting the dates in a table for the next several years. Since I am only concerned with EST and BST, however, and both follow strict rules, I was hopeing to write a function that I can use dynamically.

Hate to reinvent the wheel though.

TIAI have one I can send you later today.

blindman|||You da (blind)man!|||if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BeginDST]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[BeginDST]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EndDST]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[EndDST]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

create function BeginDST(@.TargetDate as datetime)
returns datetime
as
--function BeginDST
--blindman, 9/2003
--Returns the data Daylight Savings Time begins for the specified year.
begin
declare @.BeginDST datetime
set @.BeginDST = '4/1/' + cast(Year(@.TargetDate) as char(4))
while datename(weekday,@.BeginDST) <> 'Sunday'
set @.BeginDST = dateadd(day, +1, @.BeginDST)
Return @.BeginDST
end

GO

create function EndDST(@.TargetDate as datetime)
returns datetime
as
--function EndDST
--blindman, 9/2003
--Returns the data Daylight Savings Time ends for the specified year.
begin
declare @.EndDST datetime
set @.EndDST = '10/31/' + cast(Year(@.TargetDate) as char(4))
while datename(weekday,@.EndDST) <> 'Sunday'
set @.EndDST = dateadd(day, -1, @.EndDST)
Return @.EndDST
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO|||Thanks dude. While I was waiting, however, I came up with this:

CREATE FUNCTION [TZCONVERT] (@.time_zone VARCHAR(5), @.in_date DATETIME)
RETURNS DATETIME AS
BEGIN

DECLARE @.out_date DATETIME,
@.daylight_start_date DATETIME,
@.daylight_end_date DATETIME

IF @.time_zone = 'EST'
BEGIN
SET @.daylight_start_date = DATEADD(hour, 3, DATEADD(d, (7-DATEPART(dw,'4/1/' + CAST(YEAR(@.in_date) AS VARCHAR(4)))+2)%7-1, '4/1/' + CAST(YEAR(@.in_date) AS VARCHAR(4))))
SET @.daylight_end_date = DATEADD(hour, 1, DATEADD(day, -1 * DATEPART(dw,'10/31/' + CAST(YEAR(@.in_date) AS VARCHAR(4))), '11/1/' + CAST(YEAR(@.in_date) AS VARCHAR(4))))

IF @.in_date BETWEEN @.daylight_start_date AND @.daylight_end_date
SET @.out_date = DATEADD(hour, -4, @.in_date)
ELSE
SET @.out_date = DATEADD(hour, -5, @.in_date)
END

IF @.time_zone = 'BST'
BEGIN
SET @.daylight_start_date = DATEADD(hour, 3, DATEADD(day, -1 * DATEPART(dw,'3/31/' + CAST(YEAR(@.in_date) AS VARCHAR(4))), '4/1/' + CAST(YEAR(@.in_date) AS VARCHAR(4))))
SET @.daylight_end_date = DATEADD(hour, 1, DATEADD(day, -1 * DATEPART(dw,'10/31/' + CAST(YEAR(@.in_date) AS VARCHAR(4))), '11/1/' + CAST(YEAR(@.in_date) AS VARCHAR(4))))

IF @.in_date BETWEEN @.daylight_start_date AND @.daylight_end_date
SET @.out_date = DATEADD(hour, 1, @.in_date)
ELSE
SET @.out_date = @.in_date
END

RETURN @.out_date

END|||I tried using modulo arthimetic at first too, but it became too confusing to try to account for the fact that the "dw" parameter for DATEPART returns different values on different systems depending on the value of the DATEFIRST setting.

As long as you never run your code on a system with a different setting you should be OK.

blindman