Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2002
    Location
    Buffalo, New York
    Posts
    2

    Question Unanswered: T-SQL and Daylight Savings Question

    To determine if a timestamp is subject to daylight savings or not, you need to see if it falls between the First Sunday of April at 2am and the Last Sunday of October at 2am.

    The challenge for me is determining those dates in T-SQL code. What I really want is a way to programatically determine what those dates are (besides hard coding date ranges into a table).

    I am using SQL 2000.

    Any tips or suggestions are welcomed.

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    first of april will be
    convert(varchar(4),datepart(yy,getdate()) + '0401'

    first sunday in april will be something like

    dateadd(dd,7 - (datepart(dw,convert(varchar(4),datepart(yy,getdat e()) + '0401'), convert(varchar(4),datepart(yy,getdate()) + '0401')

    I'll leave you to figure out the exact values.
    Similar for the other date.

  3. #3
    Join Date
    Mar 2002
    Location
    Buffalo, New York
    Posts
    2

    Final Code

    Thanks for the help!
    Here is what I ended up with - it seems to work great.

    --------------------------------------------------------------
    -- DST Starts the First Sunday of April at 2am
    --------------------------------------------------------------
    SELECT @wkYear = Datepart(yyyy, @Start_Date);
    SELECT @Apr_1 = convert(varchar(18),datepart(yyyy,@Start_Date)) + '0401 02:00:00';
    SELECT @DayOfTheWeek = datepart(dw,@Apr_1);
    SELECT @DateDifference = 8 - @DayOfTheWeek;
    SELECT @DST_Start = dateadd(dd,@DateDifference, @Apr_1);

    --------------------------------------------------------------
    -- DST Ens the Last Sunday of October at 2am.
    --------------------------------------------------------------
    SELECT @Nov_1 = convert(varchar(18),datepart(yyyy,@Start_Date)) + '1101 02:00:00';
    SELECT @DayOfTheWeek = datepart(dw,@Nov_1);
    SELECT @DateDifference = 1 - @DayOfTheWeek;
    SELECT @DST_End = dateadd(dd,@DateDifference, @Nov_1);

  4. #4
    Join Date
    Nov 2002
    Location
    Brooklyn NY
    Posts
    7

    Re: Final Code

    based upon the above information i've written a user-defined function to calculate the adjusted DST date/time based on the given date. thanks, this information (and forum) really helped.

    CREATE FUNCTION udfGetLocalDateTime (
    @datToCheck datetime
    ) RETURNS datetime
    AS
    BEGIN

    DECLARE @datLocalDateTime datetime

    DECLARE @intYear integer
    DECLARE @strApril1 varchar(18)
    DECLARE @strNov1 varchar(18)
    DECLARE @DayOfTheWeek integer
    DECLARE @DateDifference integer
    DECLARE @datDSTStarts datetime
    DECLARE @datDSTEnds datetime
    DECLARE @intGMTOffset integer

    /* Calculate when DST begins for the year in question */
    SET @intYear = DATEPART(yyyy, @datToCheck);
    SET @strApril1 = CONVERT(varchar(18), @intYear) + '0401 02:00:00';
    SET @DayOfTheWeek = DATEPART(dw, @strApril1); /* Day April 1 falls on in that year */
    SET @DateDifference = 8 - @DayOfTheWeek; /* # of days between that day and the following Sunday ("the first Sunday in April", i.e. when DST begins)*/
    SET @datDSTStarts = DATEADD(dd, @DateDifference, @strApril1);

    /* Calculate when DST is over for the year in question */
    SET @strNov1 = CONVERT(varchar(18), @intYear) + '1101 02:00:00';
    SET @DayOfTheWeek = DATEPART(dw, @strNov1); /* Day Nov 1 falls on in that year */
    SET @DateDifference = 1 - @DayOfTheWeek; /* # of days between that day and the previous Sunday ("the last Sunday in October", i.e. when DST ends) */
    SET @datDSTEnds = DATEADD(dd, @DateDifference, @strNov1);

    /* Determine if the date in question is in DST or not */
    IF @datToCheck BETWEEN @datDSTStarts AND @datDSTEnds
    SET @intGMTOffset = -6 /* MDT */
    ELSE
    SET @intGMTOffset = -7 /* MST */

    SET @datLocalDateTime = DATEADD(hh, @intGMTOffset, @datToCheck)

    RETURN @datLocalDateTime

    END

Posting Permissions

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