2014/11/20

If you ever need to determine daylight saving time in Europe, here's the T-SQL script

Or, as the Dutch call it, 'zomertijd' and 'wintertijd'

CREATE TABLE #daylight_saving (
  date_utc DATETIME,
  is_dst BIT,
  date_cet DATETIME
)

INSERT INTO #daylight_saving (date_utc, is_dst, date_cet)
VALUES
('2014-11-20 14:36', 0, '2014-11-20 15:36')
, ('2014-11-20 23:59', 0, '2014-11-21 00:59')
, ('2014-03-30 00:00', 0, '2014-03-30 01:00')
, ('2014-03-30 00:59:59', 0, '2014-03-30 01:59:59')
, ('2014-03-30 01:00:00', 1, '2014-03-30 03:00:00')
, ('2014-10-26 00:59:59', 1, '2014-10-26 02:59:59')
, ('2014-10-26 01:00:00', 0, '2014-10-26 02:00:00')

SELECT
  CASE
    WHEN date_utc <
      DATEADD(
        HOUR
        , 1
        , DATEADD(
          DAY
          , -DATEDIFF(
            DAY
            , 6
            , CAST(YEAR(date_utc) AS char(4)) + '0331 00:00:00' -- last day of March
          ) % 7
          , CAST(YEAR(date_utc) AS char(4)) + '0331 00:00:00' -- last day of March
        ) -- last Sunday of March
      ) -- last Sunday of March 01:00 UTC = DST start
    OR date_utc >=
      DATEADD(
        HOUR
        , 1
        , DATEADD(
          DAY
          , -DATEDIFF(
            DAY
            , 6
            , CAST(YEAR(date_utc) AS char(4))+ '-10-31 00:00:00' -- last day of October
          ) % 7
          , CAST(YEAR(date_utc) AS char(4))+ '-10-31 00:00:00' -- last day of October
        ) -- last Sunday of October
      ) -- last Sunday of October 01:00 UTC = DST end
    THEN DATEADD (HOUR, 1, date_utc)
    ELSE DATEADD(HOUR, 2, date_utc)
    END date_cet_calculated
  , date_cet
  , date_utc
  , is_dst
FROM #daylight_saving


No comments: