How to calculate the local datetime from a utc datetime in tsql (sql 2005)?

Question

Hi,

i want to loop over a period of time in tsql, and print the utc datetimes and our local variant. We live in UTC +1, so i could easily add 1 hour, but in the summertime we live in UTC +2.

In C# i can create a datetime and use a method to ask for the UTC variant and vice versa.

Till now i have this:

declare @counter int
declare @localdate datetime
declare @utcdate datetime
 set @counter = 0
 while @counter < 100
 begin
   set @counter = @counter + 1
   print 'The counter is ' + cast(@counter as char)
  set @utcdate  = DATEADD(day,@counter,GETUTCDATE())
  --set @localdate = ????

  print  @localdate  
  print @utcdate
 end

Answer 1

GETUTCDATE() just gives you the current time in UTC, any DATEADD() you do to this value will not include any daylight savings time shifts.

Your best bet is build your own UTC conversion table or just use something like this:

http://www.codeproject.com/KB/database/ConvertUTCToLocal.aspx

Answer 2

Assuming you are using SQL 2005 upwards, you can develop a SQL CLR function to take a UTC date and convert to the local date.

This link is an MSDN How-To explaining how you can create a scalar UDF in C#.

Create a SQL function along the lines of

[SqlFunction()]
public static SqlDateTime ConvertUtcToLocal(SqlDateTime utcDate) 
{
    // over to you to convert SqlDateTime to DateTime, specify Kind
    // as UTC, convert to local time, and convert back to SqlDateTime
}

Your sample above would then become

set @localdate = dbo.ConvertUtcToLocal(@utcdate)

SQL CLR has its overheads in terms of deployment, but I feel cases like this are where it fits in best.