If you ever need to determine daylight saving time in Europe, here's the T-SQL script
20 juni 2015 2015-06-20 13:29If you ever need to determine daylight saving time in Europe, here's the T-SQL script
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