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

This snippet was shared earlier at my 'first' blog http://techgineer.blogspot.nl/2014/11/if-you-ever-need-to-determine-daylight.html

Founder of this blog. Business Intelligence consultant, developer, coach, trainer and speaker at events. Currently working at Dura Vermeer. Loves to explain things, providing insight in complex issues. Watches the ongoing development of the Microsoft Business Intelligence stack closely. Keeping an eye on Big Data, Data Science and IoT.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Next ArticleGathering knowledge and keeping up