USE [DATABASE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Leonard Nelson
-- Create date: 02/08/2011
-- Description: Convert Epoch Time to Local Time.
-- Definition: Epoch time is the number of seconds that have elapsed since January 1, 1970 (midnight UTC/GMT)
-- Syntax: SELECT dbo.FunctionConvertToLocalTime('1295472435')
-- 1/19/2011 4:27:15 PM Eastern Standard Time = 1295472435
-- =============================================
ALTER FUNCTION [dbo].[FunctionConvertToLocalTime]
(
@InputEpochTime int
)
RETURNS datetime
AS
BEGIN
DECLARE @CurrentTimeZoneOffset int, @TimeZoneOffset int, @LocalTime datetime, @UTCTime datetime;
-- Add the Epoch Time seconds value to the '1970-01-01 00:00:00'
SET @UTCTime = DATEADD(second, @InputEpochTime, CAST('1970-01-01 00:00:00' as datetime));
-- Set the Time Zone Offset
SET @CurrentTimeZoneOffset = DATEDIFF(hh, GETUTCDATE(), GETDATE());
-- Set the Eastern Time Zone and factor for Daylight Savings Time Offset
SET @TimeZoneOffset =
CASE
-- The schedule through 2006 in the United States was that DST began on the first Sunday in April
-- (April 2, 2006), and changed back to standard time on the last Sunday in October (October 29, 2006).
-- The time is adjusted at 02:00 local time.
WHEN YEAR(@UTCTime) < = 2006 THEN
CASE
WHEN
CAST(@UTCTime AS datetime) >= '4/' + CAST(ABS(8-DATEPART(dw,'4/1/' + CAST(YEAR(@UTCTime) as varchar)))%7 + 1 as varchar) + '/' + CAST(YEAR(@UTCTime) as varchar) + ' 2:00'
AND
CAST(@UTCTime AS datetime) < '10/' + CAST(32-DATEPART(dw,'10/31/' + CAST(YEAR(@UTCTime) as varchar)) as varchar) + '/' + CAST(YEAR(@UTCTime) as varchar) + ' 2:00'
THEN
-- Time Zone EST DST Offset should be 4
-4
ELSE
-- Time Zone EST DST Offset should be 5
-5
END
ELSE
CASE
-- By the Energy Policy Act of 2005, daylight saving time (DST) was extended in the United States in 2007.
-- DST starts on the second Sunday of March, which is three weeks earlier than in the past, and it ends on
-- the first Sunday of November, one week later than in years past. This change resulted in a new DST period
-- that is four weeks (five in years when March has five Sundays) longer than in previous years.[35] In 2008
-- daylight saving time ended at 02:00 on Sunday, November 2, and in 2009 it began at 02:00 on Sunday, March 8.[36]
WHEN
CAST(@UTCTime AS datetime) >= '3/' + CAST(ABS(8-DATEPART(dw,'3/1/' + CAST(YEAR(@UTCTime) as varchar)))%7 + 8 as varchar) + '/' + CAST(YEAR(@UTCTime) as varchar) + ' 2:00'
AND
CAST(@UTCTime AS datetime) < '11/' + CAST(abs(8-DATEPART(dw,'11/1/' + CAST(YEAR(@UTCTime) as varchar)))%7 + 1 as varchar) + '/' + CAST(YEAR(@UTCTime) as varchar) + ' 2:00'
THEN
-- Time Zone EST DST Offset should be 4
-4
ELSE
-- Time Zone EST DST Offset should be 5
-5
END
END
IF @TimeZoneOffset > 0
BEGIN
SET @TimeZoneOffset = ABS(@TimeZoneOffset);
END
ELSE
BEGIN
SET @TimeZoneOffset = -ABS(@TimeZoneOffset);
END
-- Add the Time Zone DST Offset to the Input Time Stamp to calculate the UTC Time
SET @LocalTime = DATEADD(hour, @TimeZoneOffset, @UTCTime);
-- Convert Local Time to 24 hour format yyyy-mm-dd hh:mi:ss
SET @LocalTime = CONVERT(datetime, @LocalTime, 120);
-- Return the LocalTime
RETURN (@LocalTime);
END