Thursday, October 13, 2011

Timezone conversion UTC to CST with Daylight Savings

Converting UTC format date to Standard time of CST or EST is straight forward.

DECLARE @UTC_Date DATETIME
SET @UTC_Date = GETUTCDATE()
SELECT
@UTC_Date AS [UTC],
DATEADD(hh, -6, @UTC_Date) AS [CST - Standard Time],
DATEADD(hh, -5, @UTC_Date) AS [EST - Standard Time]

But if the given date falls under daylight saving then the above calculation won't work.

So how is Daylight saving calculated?

1. If the year <= 2006 then daylight saving is between:
2 am on First Sunday in April till 2 am on Last Sunday in October

2. If the year >= 2007 then daylight saving is between:
2 am on Second Sunday in March till 2 am on First Sunday in November

3. UTC to CST (Standard Time) = -6
4. UTC to CDT (Daylight Time) = -5

5. UTC to EST (Standard Time) = -5
6. UTC to EDT (DayLight Time) = -4

Solution - 1:

DECLARE @UTC_Date DATETIME
SET @UTC_Date = GETUTCDATE()


SELECT
@UTC_Date AS [UTC],
DATEADD(hh, -6, @UTC_Date) AS [CST - Standard Time],
DATEADD(hh,
CASE WHEN YEAR(@UTC_Date) <= 2006 THEN
                CASE WHEN
                      @UTC_Date >=  '4/' + CAST(ABS(8 - DATEPART(WEEKDAY,'4/1/'
+ CAST(YEAR(@UTC_Date) AS VARCHAR))) % 7 + 1 AS VARCHAR) +  '/'
+ CAST(YEAR(@UTC_Date) AS VARCHAR) + ' 2:00' AND
                      @UTC_Date < '10/' + CAST(32 - DATEPART(WEEKDAY,'10/31/'
+ CAST(YEAR(@UTC_Date) AS VARCHAR)) AS VARCHAR) +  '/'
+ CAST(YEAR(@UTC_Date) AS VARCHAR) + ' 2:00'
                THEN -5 ELSE -6 END
              ELSE
                CASE WHEN
                      @UTC_Date >= '3/' + CAST(ABS(8 - DATEPART(WEEKDAY,'3/1/'
+ CAST(YEAR(@UTC_Date) AS VARCHAR))) % 7 + 8 AS VARCHAR) +  '/'
+ CAST(YEAR(@UTC_Date) AS VARCHAR) + ' 2:00' AND
                      @UTC_Date <
                        '11/' + CAST(ABS(8 - DATEPART(WEEKDAY,'11/1/'
+ CAST(YEAR(@UTC_Date) AS VARCHAR))) % 7 + 1 AS VARCHAR) +  '/'
+ CAST(YEAR(@UTC_Date) AS VARCHAR) + ' 2:00'
                THEN -5 ELSE -6 END
              END
, @UTC_Date
) AS [CST/CDT - DayLight Time],
DATEADD(hh, -5, @UTC_Date) AS [EST - Standard Time],
DATEADD(hh,
CASE WHEN YEAR(@UTC_Date) <= 2006 THEN
                CASE WHEN
                      @UTC_Date >=  '4/' + CAST(ABS(8 - DATEPART(WEEKDAY,'4/1/'
+ CAST(YEAR(@UTC_Date) AS VARCHAR))) % 7 + 1 AS VARCHAR) +  '/'
+ CAST(YEAR(@UTC_Date) AS VARCHAR) + ' 2:00' AND
                      @UTC_Date < '10/' + CAST(32 - DATEPART(WEEKDAY,'10/31/'
+ CAST(YEAR(@UTC_Date) AS VARCHAR)) AS VARCHAR) +  '/'
+ CAST(YEAR(@UTC_Date) AS VARCHAR) + ' 2:00'
                THEN -5 ELSE -6 END
              ELSE
                CASE WHEN
                      @UTC_Date >= '3/' + CAST(ABS(8 - DATEPART(WEEKDAY,'3/1/'
+ CAST(YEAR(@UTC_Date) AS VARCHAR))) % 7 + 8 AS VARCHAR) +  '/'
+ CAST(YEAR(@UTC_Date) AS VARCHAR) + ' 2:00' AND
                      @UTC_Date <
                        '11/' + CAST(ABS(8 - DATEPART(WEEKDAY,'11/1/'
+ CAST(YEAR(@UTC_Date) AS VARCHAR))) % 7 + 1 AS VARCHAR) +  '/'
+ CAST(YEAR(@UTC_Date) AS VARCHAR) + ' 2:00'
                THEN -4 ELSE -5 END
              END
, @UTC_Date
) AS [EST/EDT - DayLight Time]
GO

Instead of calling the same set of code again and again we can make it as a User defined function and call it by passing UTC date, Offset values for standard time and daylight saving respectively.

Solution - 2

/* Parameter 1 = UTC Date
Parameter 2 = Offset value for Standard time
Parameter 3 = Offset value for Daylight saving time */

CREATE FUNCTION [dbo].[fn_GetDaylightSavingsTime]
(
@UTC_Date DATETIME,
@ST_Offset INT, -- CST = -6, EST = -5
@DT_Offset INT  -- CDT = -5, EDT = -4
)
RETURNS DATETIME
AS
BEGIN


RETURN 
DATEADD(hh, 
CASE WHEN YEAR(@UTC_Date) <= 2006 THEN  
                CASE WHEN 
                      @UTC_Date >=  '4/' + CAST(ABS(8 - DATEPART(WEEKDAY,'4/1/' 
+ CAST(YEAR(@UTC_Date) AS VARCHAR))) % 7 + 1 AS VARCHAR) +  '/' 
+ CAST(YEAR(@UTC_Date) AS VARCHAR) + ' 2:00' AND 
                      @UTC_Date < '10/' + CAST(32 - DATEPART(WEEKDAY,'10/31/' 
+ CAST(YEAR(@UTC_Date) AS VARCHAR)) AS VARCHAR) +  '/' 
+ CAST(YEAR(@UTC_Date) AS VARCHAR) + ' 2:00' 
                THEN @DT_Offset ELSE @ST_Offset END
              ELSE
                CASE WHEN 
                      @UTC_Date >= '3/' + CAST(ABS(8 - DATEPART(WEEKDAY,'3/1/' 
+ CAST(YEAR(@UTC_Date) AS VARCHAR))) % 7 + 8 AS VARCHAR) +  '/' 
+ CAST(YEAR(@UTC_Date) AS VARCHAR) + ' 2:00' AND 
                      @UTC_Date < 
                        '11/' + CAST(ABS(8 - DATEPART(WEEKDAY,'11/1/' 
+ CAST(YEAR(@UTC_Date) AS VARCHAR))) % 7 + 1 AS VARCHAR) +  '/' 
+ CAST(YEAR(@UTC_Date) AS VARCHAR) + ' 2:00' 
                THEN @DT_Offset ELSE @ST_Offset END
              END
, @UTC_Date
)
END
GO

Usage:

DECLARE @UTC_Date DATETIME
DECLARE @ST_Offset INT, @DT_Offset INT


SET @UTC_Date = GETUTCDATE()
SET @ST_Offset = -6
SET @DT_Offset = -5


SELECT [dbo].[fn_GetDaylightSavingsTime](@UTC_Date, @ST_Offset, @DT_Offset)

Reference:

1 comment:

Anonymous said...

Just noticed that you have an error in your EST/EDT calculation in solution 1. In the 2006 and earlier section, you have the time modified by the amounts for central time. The post 2006 section is fine.