Skip to content

Instantly share code, notes, and snippets.

Created May 1, 2015
Embed
What would you like to do?
Convert UTC to local time (in GMT time zone) taking into account daylight savings.
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment