UTC datetime values in SQL Server 2000

You can convert local datetime values to UTC datetime values, and vice-versa, using the built-in GETUTCDATE() function:

DECLARE @LocalDate DATETIME
SET @LocalDate = GETDATE()

-- convert local date to utc date
DECLARE @UTCDate DATETIME
SET @UTCDate = DATEADD(Hour, DATEDIFF(Hour, GETUTCDATE(), GETDATE()), @LocalDate)

-- convert utc date to local date
DECLARE @LocalDate2 DATETIME
SET @LocalDate2 = DATEADD(Hour, DATEDIFF(Hour, GETDATE(), GETUTCDATE()), @UTCDate)

SELECT @LocalDate, @UTCDate, @LocalDate2

Note that GETUTCDATE() returns the current datetime in UTC.  By comparing the value with GETDATE() we can determine the time zone, which can then be used to adjust any date. 

I tried to bake these expressions into a set of user-defined functions, but SQL Server complained because user-defined functions cannot call non-deterministic functions (in this case GETDATE()/GETUTCDATE()).