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
