If the server and the client are in the same time zone then you can use a much simpler conversion that looks at the server time difference between the current time stamps (GETDATE() and GETUTCDATE()). See the two scripts to create the functions below.
/****** Object: UserDefinedFunction [dbo].[GetLocalFromUTC] Script Date: 06/27/2011 19:34:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Darren Ash
-- Create date: 06/27/2011
-- Description: UTC Function
-- =============================================
CREATE FUNCTION [dbo].[GetLocalFromUTC]
(
-- Add the parameters for the function here
@UTCTimeStamp datetime
)
RETURNS datetime
AS
BEGIN
-- Declare the return variable here
DECLARE @LocalTimeStamp datetime
-- Add the T-SQL statements to compute the return value here
SELECT @LocalTimeStamp = DATEADD(Hour, DATEDIFF(Hour, GETUTCDATE(), GETDATE()), @UTCTimeStamp)
-- Return the local time stamp for the server based on the provided UTC Value
RETURN @LocalTimeStamp
END
GO
