Skip to content

Instantly share code, notes, and snippets.

/UTCToLocalTime

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