Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256

    Unanswered: 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.

    TIA
    -bpd

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I have one I can send you later today.

    blindman

  3. #3
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    You da (blind)man!
    -bpd

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

  5. #5
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    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
    -bpd

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •