Simple UTC & Local Conversion

Post date: Jun 28, 2011 12:39:15 AM

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