General Information‎ > ‎SQL Server‎ > ‎

Simple UTC & Local Conversion

posted Jun 27, 2011, 5:39 PM by Darren Ash   [ updated Jun 27, 2011, 5:44 PM ]
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


Comments