I had a question from someone about how to convert from UTCTime to local time in T-SQL code. Here is one way to do it:
— Convert a UTC Time to a Local Time
DECLARE @UTCDate datetime
DECLARE @LocalDate datetime
DECLARE @TimeDiff int
— Figure out the time difference between UTC and Local time
SET @UTCDate = GETUTCDATE()
SET @LocalDate = GETDATE()
SET @TimeDiff = DATEDIFF(hh, @UTCDate, @LocalDate)
— Check Results
PRINT @LocalDate
PRINT @UTCDate
PRINT @TimeDiff
— Convert UTC to local time
DECLARE @DateYouWantToConvert datetime
DECLARE @ConvertedLocalTime datetime
SET @DateYouWantToConvert = ‘4/25/2007 18:00’
SET @ConvertedLocalTime = DATEADD(hh, @TimeDiff, @DateYouWantToConvert)
— Check Results
PRINT @ConvertedLocalTime
Technorati Tag: SQL Server
Glenn Berry's SQL Server Performance 
Hi!
I was looking for the same functionality. If I am right your code finds out the offset between UTC and local time using current localtime and current UTC. If we are in summer and using daylight saving time the difference is not the same as in the winter. Your function is returning different results during winter than during daylight saving time period.
I have written a small function which is returning the offset for central Europe. It can be easily adapted for other time regions as long as daylight saving time is switched on the last sunday in march and on the last sunday in october. The formulae for calculating last sunday work only until 2099, but that is fine for me. 🙂
CREATE FUNCTION [dbo].[getUTCOffset] (@d datetime)RETURNS intASBEGIN DECLARE @Result int
DECLARE @spring datetime DECLARE @autumn datetime
— 31 – (5 * year / 4 + 4) mod 7 — day of the last sunday in march
select @spring = cast (convert (varchar (4), year (@d)) + \’-03-\’ + convert (varchar (2), 31 – (floor (5 * year (@d) / 4) + 4) % 7) + \’ 02:00:00\’ as datetime)
— 31 – (5 * year / 4 + 1) mod 7 — day of the last sunday in october select @autumn = cast (convert (varchar (4), year (@d)) + \’-10-\’ + convert (varchar (2), 31 – (floor (5 * year (@d) / 4) + 1) % 7) + \’ 02:00:00\’ as datetime)
if @d between @spring and @autumn select @Result = -2 else select @Result = -1
RETURN @ResultEND
http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!492.entry
If you just want to change it to the system local time, a simple, inquery route it to use this:
SELECT DATEADD(minute, DATEDIFF(minute,getutcdate(),getdate()), UTC_Time_Value_You_Want_In_Local)
Thanks Ben.