It’s Time – Checking and Adjusting for Daylight Saving Time

Ever need to join data sources from different time zones? Here’s a scenario I recently faced, and how I worked around it.

I needed to join several tables together from two different data sources. One data source was captured in UTC, while the other was local time zone, including Daylight Saving Time (DST) considerations. An additional challenge was that the data source that was in local time zone, can be in different time zones. I did have access to a geography dimension that had the time zones and the number of hours offset from UTC.

I know that there are SQL Server specific time offset options, but for a variety of reasons, I needed a more generic option to deal with the time zone and DST factors. That meant I needed to create an approach to check to see if a datetime was in Daylight Saving Time or not. I thought I would be able to find a reference table, but that meant I would need to be able to build one.

The code

Here’s the code I wrote to build a temp table that had the DST Start and DST End datetimes for each year I needed to compare, and you can see how I did the compare to change the time to DST:

IF OBJECT_ID('tempdb..#DST_Date') IS NOT NULL
DROP TABLE #DST_Date;

CREATE TABLE #DST_Date(
   Date_Key  datetime NOT NULL
 , DstStart  datetime NOT NULL
 , DstEnd  datetime NOT NULL
) ON [PRIMARY]
declare   @i               INT 
        , @date            DATETIME
        , @enddate         DATETIME
        , @StartDate       DATETIME
        , @year            INT 
        , @StartOfMarch    DATETIME 
        , @StartOfNovember DATETIME 
        , @DstStart        DATETIME 
        , @DstEnd          DATETIME
set @i = (select DATEDIFF(dd,'2018-01-01','2018-01-01'))                            --  Set the starting date of the table
set @StartDate ='2018-01-01'
set @enddate = DATEADD(yy,(DATEDIFF(yy,'2018-01-01', getdate())) + 1,'2018-12-31')  -- One year more than current year
while @i <= (select DATEDIFF(dd,'2018-01-01',@enddate))                             --  Set the ending date of the table
begin
set @year = DATEPART(YYYY,(DATEADD(dd,@i,@StartDate)))
SET @StartOfMarch = DATEADD(MONTH, 2, DATEADD(YEAR, @year - 1900, 0))
SET @StartOfNovember = DATEADD(MONTH, 10, DATEADD(YEAR, @year - 1900, 0));
SET @DstStart = DATEADD(HOUR, 2,
     DATEADD(day,
       ( ( 15 - DATEPART(dw, @StartOfMarch) ) % 7 )
        + 7, @StartOfMarch))
SET @DstEnd   = DATEADD(HOUR, 2,
     DATEADD(day,
       ( ( 8 - DATEPART(dw, @StartOfNovember) ) % 7 ),
        @StartOfNovember))
SET @date =  (DATEADD(dd,@i,@StartDate))
INSERT INTO #DST_Date 
(
   [Date_Key],
   [DstStart],
   [DstEnd] 
)
VALUES
(
  DATEADD(dd,@i,@StartDate),
      @DstStart, 
      @DstEnd
)  
   set @i = @i + 1
 end;

DECLARE @testDate    datetime = '2018-03-11 01:01:00.000',
        @testDSTDate datetime = '2018-03-11 02:01:00.000'
   
select Date_Key, DstStart, DstEnd , 
   @testDate                                                            AS testDate  ,
    CASE 
     WHEN  @testDate Between DstStart AND DstEnd
     THEN 1
     ELSE 0
    END                                                                 AS IN_DST,
    CASE 
     WHEN @testDate Between DstStart AND DstEnd
     THEN (DATEADD(ss,(3600), @testDate)) 
     ELSE  @testDate
    END                                                                 AS testDateDST_Time,
      @testDSTDate                                                      AS testDSTDate  ,
    CASE 
     WHEN  @testDSTDate Between DstStart AND DstEnd
     THEN 1
     ELSE 0
    END                                                                 AS DSTin_DST,
    CASE 
     WHEN @testDSTDate Between DstStart AND DstEnd
     THEN (DATEADD(ss,(3600), @testDSTDate)) 
     ELSE  @testDSTDate
    END                                                                 AS testDSTDateDST_Time
 from #DST_Date 
where CAST( @testDate  as DATE) = Date_Key

SELECT * FROM #DST_Date

Here is the output of the test and a sample of the DST_Test temp table: