DST T-SQL

-- this code returns the start and end datetime’s for DST in the current year
-- DST begins at 2am on the SECOND SUNDAY of March and ends the FIRST SUNDAY of November

SELECT 
  DATEADD(HOUR, (MULTIPLIER + 7 /*skip the first week*/) * 24 /*24 hours a day*/ + 2 /*offset to 2am*/, StartWeek) AS StartDate, 
  DATEADD(HOUR, MULTIPLIER * 24 + 2, EndWeek) AS EndDate 
FROM ( 
  SELECT 0 AS MULTIPLIER, 1 AS DOW UNION ALL 
  SELECT 6, 2 UNION ALL 
  SELECT 5, 3 UNION ALL 
  SELECT 4, 4 UNION ALL 
  SELECT 3, 5 UNION ALL 
  SELECT 2, 6 UNION ALL 
  SELECT 1, 7 
) dow, 
(SELECT 
  '03/01/' + convert(varchar, DATEPART(YEAR, GETDATE())) AS StartWeek, 
  '11/01/' + convert(varchar, DATEPART(YEAR, GETDATE())) AS EndWeek 
) DST 
WHERE DATEPART(WEEKDAY, StartWeek) = DOW

Leave a Reply