How can I get the correct offset between UTC and local times for a date that is before or after DST?

Question

I currently use the following to get a local datetime from a UTC datetime:

SET @offset = DateDiff(minute, GetUTCDate(), GetDate())
SET @localDateTime = DateAdd(minute, @offset, @utcDateTime)

My problem is that if daylight savings time occurs between GetUTCDate() and @utcDateTime, the @localDateTime ends up being an hour off.

Is there an easy way to convert from utc to local time for a date that is not the current date?

I'm using SQL Server 2005

Answer 1

The best way to convert a non-current UTC date into local time is to use the CLR. The code itself is easy; the difficult part is usually convincing people that the CLR isn't pure evil or scary...

For one of the many examples, check out Harsh Chawla's blog post on the topic.

Unfortunately, there is nothing built-in that can handle this type of conversion, save for CLR-based solutions. You could write a T-SQL function which does something like this, but then you'd have to implement the date-change logic yourself, and I'd call that decidedly not easy.

Answer 2

I found this answer on StackOverflow that provides a User Defined Function that appears to accurately translate the datetimes

The only thing you need to modify is the @offset variable at the top to set it to the Timezone offset of the SQL server running this function. In my case, our SQL server uses EST, which is GMT - 5

It's not perfect and probably won't work for many cases such has half-hour or 15-minute TZ offsets (for those I'd recommend a CLR function like Kevin recommended), however it works well enough for most generic time zones in North America.

CREATE FUNCTION [dbo].[UDTToLocalTime](@UDT AS DATETIME)  
RETURNS DATETIME
AS
BEGIN 
--====================================================
--Set the Timezone Offset (NOT During DST [Daylight Saving Time])
--====================================================
DECLARE @Offset AS SMALLINT
SET @Offset = -5

--====================================================
--Figure out the Offset Datetime
--====================================================
DECLARE @LocalDate AS DATETIME
SET @LocalDate = DATEADD(hh, @Offset, @UDT)

--====================================================
--Figure out the DST Offset for the UDT Datetime
--====================================================
DECLARE @DaylightSavingOffset AS SMALLINT
DECLARE @Year as SMALLINT
DECLARE @DSTStartDate AS DATETIME
DECLARE @DSTEndDate AS DATETIME
--Get Year
SET @Year = YEAR(@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'
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)

--Get DaylightSavingOffset
SET @DaylightSavingOffset = CASE WHEN @LocalDate BETWEEN @DSTStartDate AND @DSTEndDate THEN 1 ELSE 0 END

--====================================================
--Finally add the DST Offset 
--====================================================
RETURN DATEADD(hh, @DaylightSavingOffset, @LocalDate)
END



GO

Answer 3

There are a couple of good answers to a similar question asked on Stack Overflow. I wound up using a T-SQL approach from the second answer by Bob Albright to clean up a mess caused by a data conversion consultant.

It worked for almost all of our data, but then I later realized that his algorithm only works for dates as far back as April 5, 1987, and we had some dates from the 1940s that still didn't convert properly. We ultimately needed the UTC dates in our SQL Server database to line up with an algorithm in a 3rd party program that used the Java API to convert from UTC to local time.

I like the CLR example in Kevin Feasel's answer above using Harsh Chawla's example, and I'd also like to compare it to a solution that uses Java, since our front end uses Java to do the UTC to local time conversion.

Wikipedia mentions 8 different constitutional amendments that involve time zone adjustments prior to 1987, and many of those are very localized to different states, so there is a chance that the CLR and Java may interpret them differently. Does your front-end application code use dotnet or Java, or are dates before 1987 an issue for you?