PDA

View Full Version : T-SQL and Daylight Savings Question

vlafratta
03-26-02, 17:31
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.
nigelrivett
03-31-02, 20:20
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.
vlafratta
04-04-02, 17:25
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);
icebrrrg
11-12-02, 19:35
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