It’s common practice to set a database to UTC time. Coordinated Universal Time, abbreviated as UTC, is the primary time standard by which the world regulates clocks and time. For most purposes, UTC is considered interchangeable with GMT, but GMT is no longer precisely defined by the scientific community. If you don’t believe me then I refer you to the following link.
This creates problems however as UTC does not observe daylight saving time. So if you were to prepare a report regarding a company’s call data calls may show as having occurred at seven in the morning before offices had opened. When daylight savings is taken into account the calls actually occurred at eight.
SQL Server has no inbuilt conversion process to deal with this but a simple function can be created to deal with this as below.
My example deals with Ireland specifically. As countries differ in when they implement daylight savings if the company is not based in Ireland you will have to adjust the logic below to suit your needs.
I would recommend searching online and creating a table containing previous and future year values of daylight savings times and then plugging these values into the script below.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| IF OBJECT_ID('[dbo].[UTCToLocalTime]') IS NOT NULL | |
| DROP FUNCTION [dbo].[UTCToLocalTime]; | |
| GO | |
| CREATE FUNCTION [dbo].[UTCToLocalTime] (@UTC AS DATETIME) | |
| RETURNS DATETIME | |
| AS | |
| BEGIN | |
| DECLARE @Hour AS TINYINT | |
| SET @Hour = 1 | |
| –Regardless of year day light savings | |
| –is applied between April and September | |
| IF ( | |
| MONTH(@UTC) > 3 | |
| AND MONTH(@UTC) < 10 | |
| ) | |
| BEGIN | |
| SET @UTC = DATEADD(HOUR, + @Hour, @UTC) | |
| END | |
| –Regardless of year day light savings | |
| –is not applied between November and February | |
| ELSE IF ( | |
| MONTH(@UTC) > 10 | |
| AND MONTH(@UTC) < 3 | |
| ) | |
| BEGIN | |
| SET @UTC = @UTC | |
| END | |
| –If date variable is during March or October | |
| –assess if daylight savings is applied dependent on year | |
| ELSE IF ( | |
| @UTC BETWEEN '2009-03-29 01:00:00' | |
| AND '2009-10-25 02:00:00' | |
| ) | |
| OR ( | |
| @UTC BETWEEN '2010-03-28 01:00:00' | |
| AND '2010-10-31 02:00:00' | |
| ) | |
| OR ( | |
| @UTC BETWEEN '2011-03-27 01:00:00' | |
| AND '2011-10-30 02:00:00' | |
| ) | |
| OR ( | |
| @UTC BETWEEN '2012-03-25 01:00:00' | |
| AND '2012-10-28 02:00:00' | |
| ) | |
| OR ( | |
| @UTC BETWEEN '2013-03-31 01:00:00' | |
| AND '2013-10-27 02:00:00' | |
| ) | |
| OR ( | |
| @UTC BETWEEN '2014-03-30 01:00:00' | |
| AND '2014-10-26 02:00:00' | |
| ) | |
| OR ( | |
| @UTC BETWEEN '2015-03-29 01:00:00' | |
| AND '2015-10-25 02:00:00' | |
| ) | |
| OR ( | |
| @UTC BETWEEN '2016-03-27 01:00:00' | |
| AND '2016-10-30 02:00:00' | |
| ) | |
| OR ( | |
| @UTC BETWEEN '2017-03-26 01:00:00' | |
| AND '2017-10-29 02:00:00' | |
| ) | |
| OR ( | |
| @UTC BETWEEN '2018-03-25 01:00:00' | |
| AND '2018-10-28 02:00:00' | |
| ) | |
| OR ( | |
| @UTC BETWEEN '2019-03-31 01:00:00' | |
| AND '2019-10-27 02:00:00' | |
| ) | |
| BEGIN | |
| SET @UTC = DATEADD(HOUR, + @Hour, @UTC) | |
| END | |
| ELSE | |
| BEGIN | |
| SET @UTC = @UTC | |
| END | |
| RETURN @UTC | |
| END | |
Tidbytez 