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 /****** Object: UserDefinedFunction [dbo].[GetUTCFromLocal] Script Date: 06/27/2011 19:43:12 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Darren Ash -- Create date: 06/27/2011 -- Description: Get UTC from Local -- ============================================= CREATE FUNCTION [dbo].[GetUTCFromLocal] ( -- Add the parameters for the function here @LocalTimeStamp datetime ) RETURNS datetime AS BEGIN -- Declare the return variable here DECLARE @UTCTimeStamp datetime -- Add the T-SQL statements to compute the return value here SELECT @UTCTimeStamp = DATEADD(Hour, DATEDIFF(Hour, GETDATE(), GETUTCDATE()), @LocalTimeStamp) -- Return the UTC time stamp for the server based on the provided local time value RETURN @UTCTimeStamp END GO |
General Information > SQL Server >