Converting UTC Datetime Values To Local Time Zones

I would like to convert a UTC datetime value to any Local Time Zone. Below is some code I'm trying to ensure works correctly. Anyone care to comment?

I have used the following lines before the function to obtain the 2nd parameter:


SELECT GETUTCDATE()

SELECT GETDATE()

SELECT DATEDIFF(hh,'2008-05-20 20:08:01.020', '2008-05-20 16:08:01.020')

DECLARE @UTC DATETIME

EXECUTE @UTC = UTCtoLocalDate '2008-05-20 20:08:01.020', -4


-- =============================================

-- Description: <Converts local datetime values to UTC datetime values, using the built-in GETUTCDATE() function.

-- <Parameter two (@TZ) represents the time zone difference from UTC/GMT. To obtain this value

-- <run GETUTCDATE() and GETDATE(), then determine the timezone from the difference between the two.>

-- =============================================

CREATE FUNCTION [dbo].[UTCtoLocalDate] (@UTCDate DATETIME, @TZ INT)

RETURNS DATETIME AS

BEGIN

IF ( DATEPART(hh, @UTCDate) <> 0 )

BEGIN

DECLARE @LocalDate DATETIME

DECLARE @UTCDelta INT

DECLARE @thisYear INT

DECLARE @DSTDay INT

DECLARE @NormalDay INT

DECLARE @DSTDate DATETIME

DECLARE @NormalDate DATETIME

SET @thisYear = YEAR(@UTCDate)

IF (@thisYear < 2007 )

BEGIN

SET @DSTDay = ( 2 + 6 * @thisYear - FLOOR(@thisYear / 4) ) % 7 + 1

SET @NormalDay = ( 31 - ( FLOOR( @thisYear * 5 / 4) + 1) % 7)

SET @DSTDate = '4/' + CAST(@DSTDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'

SET @NormalDate = '10/' + CAST(@NormalDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'

END

ELSE

BEGIN

SET @DSTDay = ( 14 - ( FLOOR( 1 + @thisYear * 5 / 4 ) ) % 7 )

SET @NormalDay = ( 7 - ( FLOOR ( 1 + @thisYear * 5 / 4) ) % 7 )

SET @DSTDate = '3/' + CAST(@DSTDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'

SET @NormalDate = '11/' + CAST(@NormalDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'

END

IF ((@UTCDate > @DSTDate) AND (@UTCDate < @NormalDate))

BEGIN

SET @UTCDelta = @TZ + 1

END

ELSE

BEGIN

SET @UTCDelta = @TZ

END

-- now convert utc date to local date

SET @LocalDate = DATEADD(Hour, @UTCDelta, @UTCDate)

END

ELSE

BEGIN

SET @LocalDate = @UTCDate

END

RETURN(@LocalDate)

END

GO