How to convert timezones in SQL Server 2005?

Question

Hi

All my times are in UTC timezone now I need to somehow convert it to the users timezone(I have it stored in the db as well and uses the ids of the windows timezones).

How can I do this in SQL Server 2005?

Edit

So I tried to do that extended stored procedure but with Timezoneinfo I get this error

Deploy error SQL01268: .Net SqlClient Data Provider: Msg 6503, Level 16, State 12, Line 1 Assembly 'system.core, version=3.5.0.0, culture=neutral, publickeytoken=b77a5c561934e089.' was not found in the SQL catalog. An error occurred while the batch was being executed.

if I take that line out I can deploy it. Any ideas how to get around this?

Answer 1

SQL Server does not provide a simple way to convert a UTC datetime value to a local time value.

However, the page that is from includes:

A table (tbTimeZoneInfo) with data to provide the Time Zone information and two functions to convert a UTC datetime value to any Local Time Zone.

Answer 2

Wrote this just for you:

DECLARE @UTCDate DateTime /* Replace with the UTC datetime stored in your table */
DECLARE @LocalDate DateTime
DECLARE @TimeZoneOffset int /* Replace with the offset stored in your table */
SET @TimeZoneOffset = -8 /* PST */
SET @UTCDate = GETUTCDATE()
SET @LocalDate = DATEADD(Hour, @TimeZoneOffset, @UTCDate)
SELECT @UTCDate
SELECT @LocalDate

Let me know if it doesn't work.

Answer 3

Since SQL Server doesn't provide out of the box support for this, you might consider writing a .Net dll stored procedure, that makes use of the .Net TimeZoneInfo object , this object takes all rules including DST into consideration. This class allows you to convert time from one zone to another too. I hope this helps.

DateTime hwTime = new DateTime(2007, 02, 01, 08, 00, 00);
try
{
   TimeZoneInfo hwZone = TimeZoneInfo.FindSystemTimeZoneById("Hawaiian Standard Time");
   Console.WriteLine("{0} {1} is {2} local time.", 
           hwTime, 
           hwZone.IsDaylightSavingTime(hwTime) ? hwZone.DaylightName : hwZone.StandardName, 
           TimeZoneInfo.ConvertTime(hwTime, hwZone, TimeZoneInfo.Local));
}
catch (TimeZoneNotFoundException)
{
   Console.WriteLine("The registry does not define the Hawaiian Standard Time zone.");
}                           
catch (InvalidTimeZoneException)
{
   Console.WriteLine("Registry data on the Hawaiian STandard Time zone has been corrupted.");
}

[Edit]

Tutorial Creating Simple .Net DLL stored procedure.

Another useful tutorial, has more detail on deployment.

Answer 4

SQL Server 2008 would have the DATETIMEOFFSET data type (which includes the time zone) plus functions like SWITCHOFFSET to switch from one timezone offset to another.

But on the 2005 version, there's not much support for timezones.

Any chance you could upgrade any time soon??

Answer 5

I use this function:

CREATE FUNCTION fnConvertGMTToLocalTime
(
    @GMTValue   Datetime,
    @TimeZoneOffset int
)
RETURNS DateTime
AS
BEGIN
    DECLARE @LocalTime dateTime

    SELECT @LocalTime = DateAdd(Hour, @TimeZoneOffset, @GMTvalue)

    RETURN @LocalTime
END

It doesn't consider daylight savings time...but i don't need that anyway.