vreten (4/18/2011)
This is what I came up with, it converts the start time from UTC to whatever the local time is running on the sql server it is dynamic and should work on any sql server in any timezone. What is nice is when the server goes to DST this will also return the DST time.Print CONVERT(char(10), dateadd( hh, datepart(hour, GetDate()-GetUTCDate() ) - 24, '2011-04-22 17:30:00.000') , 108) as 'Activity Start Time'
This works if the date you're looking at is for an event that occurs in the same time zone as the server and does not cross the boundry between the start and end dates & times for DST.
A scenario where the problem still exists is let's say today I want to convert an Eastern time zone date from Jan 1, 2011 to UTC. Today's offset is -4 hours but the proper offset for the date I'm looking at is -5 hours. Using the current offset for the server when I analyze the date would mean my conversion is off by an hour.