TSQL: How to convert local time to UTC? (SQL Server 2008)

This works for dates that currently have the same UTC offset as SQL Server's host; it doesn't account for daylight savings changes. Replace YOUR_DATE with the local date to convert.

SELECT DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), YOUR_DATE);

7 years passed and...
actually there's this new SQL Server 2016 feature that does exactly what you need.
It is called AT TIME ZONE and it converts date to a specified time zone considering DST (daylight saving time) changes.
More info here: https://msdn.microsoft.com/en-us/library/mt612795.aspx

While a few of these answers will get you in the ballpark, you cannot do what you're trying to do with arbitrary dates for SqlServer 2005 and earlier because of daylight savings time. Using the difference between the current local and current UTC will give me the offset as it exists today. I have not found a way to determine what the offset would have been for the date in question.

That said, I know that SqlServer 2008 provides some new date functions that may address that issue, but folks using an earlier version need to be aware of the limitations.

Our approach is to persist UTC and perform the conversion on the client side where we have more control over the conversion's accuracy.

Here is the code to convert one zone DateTime to another zone DateTime

DECLARE @UTCDateTime DATETIME = GETUTCDATE();
DECLARE @ConvertedZoneDateTime DATETIME;

-- 'UTC' to 'India Standard Time' DATETIME
SET @ConvertedZoneDateTime = @UTCDateTime AT TIME ZONE 'UTC' AT TIME ZONE 'India Standard Time'
SELECT @UTCDateTime AS UTCDATE,@ConvertedZoneDateTime AS IndiaStandardTime

-- 'India Standard Time' to 'UTC' DATETIME
SET @UTCDateTime = @ConvertedZoneDateTime AT TIME ZONE 'India Standard Time' AT TIME ZONE 'UTC'
SELECT @ConvertedZoneDateTime AS IndiaStandardTime,@UTCDateTime AS UTCDATE

Note: AT TIME ZONE works only on SQL Server 2016+ and the advantage is that it automatically considers Daylight when converting to a particular Time zone

For SQL Server 2016 and newer, and Azure SQL Database, use the built in AT TIME ZONE statement.

For older editions of SQL Server, you can use my SQL Server Time Zone Support project to convert between IANA standard time zones, as listed here.

UTC to Local is like this:

SELECT Tzdb.UtcToLocal('2015-07-01 00:00:00', 'America/Los_Angeles')

Local to UTC is like this:

SELECT Tzdb.LocalToUtc('2015-07-01 00:00:00', 'America/Los_Angeles', 1, 1)

The numeric options are flag for controlling the behavior when the local time values are affected by daylight saving time. These are described in detail in the project's documentation.