Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

4 Answers 4

up vote 5 down vote accepted

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.

share|improve this answer

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
share|improve this answer

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?

share|improve this answer
    
+1 But, consider adding a link for that Wikipedia reference. The final question should probably be a comment on the original question rather than part of this answer. –  Paul White Dec 1 '12 at 5:23

I have developed and published the T-SQL Toolbox project on codeplex to help anybody who struggles with datetime and timezone handling in Microsoft SQL Server. It’s open source and completely free to use.

It offers easy datetime conversion UDFs using plain T-SQL (no CLRs) in addition with pre-filled configuration tables out of the box. And it has full DST (daylight saving time) support.

A list of all supported timezones can be found in table "DateTimeUtil.Timezone" (provided within the T-SQL Toolbox database).

In your example, you can use the following sample:

SELECT [DateTimeUtil].[UDF_ConvertUtcToLocalByTimezoneIdentifier] (
    'W. Europe Standard Time', -- the target local timezone
    '2014-03-30 00:55:00' -- the original UTC datetime you want to convert
)

This will return the converted local datetime value.

Unfortunately, it is supported for SQL Server 2008 or later only because of newer data types (DATE, TIME, DATETIME2). But as the full source code is provided you can easily adjust the tables and UDFs by replacing them by DATETIME. I don't have a MSSQL 2005 available for testing, but it should work with MSSQL 2005, too, then. In case of questions, just let me know.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.