Convert UTC time to EST time considering daylight saving time

Convert UTC time to EST time considering daylight saving time

Image result for UTC to EST

To convert UTC time into EST time directly, we may just use UTC time offsets, for EST it is -5.

--UTC to EST
declare @utcDate datetime = '2018-11-07 01:24:18.887';
SELECT GETUTCDATE() UTC, GETDATE() Local, @utcDate UTC_given, DATEADD(HH,-5,@utcDate) AS ESTTime

Considering the daylight saving, we need to apply the hour offset.

Starting in 2007, the United States and Canada will start Daylight Saving Time (DST) on the second Sunday of March and end on the first Sunday of November. From 1987 to 2006, DST began on the first Sunday in April and ended on the last Sunday of October. The logic is as following.

--Get First Possible DST StartDay
IF (@Year > 2006) SET @DSTStartDate = CAST(@Year AS CHAR(4)) + '-03-08 02:00:00'
ELSE SET @DSTStartDate = CAST(@Year AS CHAR(4)) + '-04-01 02:00:00'
--Get DST StartDate 
WHILE (DATENAME(dw, @DSTStartDate) <> 'sunday') SET @DSTStartDate = DATEADD(DAY, 1,@DSTStartDate)

--Get First Possible DST EndDate
IF (@Year > 2006) SET @DSTEndDate = CAST(@Year AS CHAR(4)) + '-11-01 02:00:00' --first Sunday in November at 2:00 AM
ELSE SET @DSTEndDate = CAST(@Year AS CHAR(4)) + '-10-25 02:00:00'
--Get DST EndDate 
WHILE (DATENAME(dw, @DSTEndDate) <> 'sunday') SET @DSTEndDate = DATEADD(DAY,1,@DSTEndDate)

The entire solution in a UDF function.

CREATE FUNCTION [dbo].[ufn_UTC_to_EST_DateTime](@prm_UTC_DateTime AS DATETIME ) RETURNS DATETIME
AS
BEGIN 
	-- From 1987 to 2006, DST began on the first Sunday in April and ended on the last Sunday of October.
	-- after 2016: Daylight Saving Time (DST) in Canada starts on the 2nd Sunday in March and ends on the 1st Sunday in November.

	DECLARE @ESTOffset SMALLINT = -5;  -- Timezone Offset
	DECLARE @LocalDate AS DATETIME = DATEADD(hh, @ESTOffset, @prm_UTC_DateTime);

	--get the DST Offset
	DECLARE @EST_Date DATETIME;
	DECLARE @Year AS SMALLINT
	DECLARE @DSTStartDate AS DATETIME
	DECLARE @DSTEndDate AS DATETIME

	SET @Year = YEAR(@LocalDate)
	if (@Year<=1987) return @LocalDate; --Get First Possible DST StartDay IF (@Year > 2006) SET @DSTStartDate = CAST(@Year AS CHAR(4)) + '-03-08 02:00:00'
	ELSE              SET @DSTStartDate = CAST(@Year AS CHAR(4)) + '-04-01 02:00:00'
	--Get DST StartDate 
	WHILE (DATENAME(dw, @DSTStartDate) <> 'sunday') SET @DSTStartDate = DATEADD(DAY, 1,@DSTStartDate)

	--Get First Possible DST EndDate
	IF (@Year > 2006) SET @DSTEndDate = CAST(@Year AS CHAR(4)) + '-11-01 02:00:00' --first Sunday in November at 2:00 AM
	ELSE              SET @DSTEndDate = CAST(@Year AS CHAR(4)) + '-10-25 02:00:00'
	--Get DST EndDate 
	WHILE (DATENAME(dw, @DSTEndDate) <> 'sunday') SET @DSTEndDate = DATEADD(DAY,1,@DSTEndDate)

	RETURN DATEADD(hh, CASE WHEN @LocalDate BETWEEN @DSTStartDate AND @DSTEndDate THEN 1 ELSE 0 END, @LocalDate)
END
go

20 total views, 1 views today

Leave a Reply