SQL Server

Subscribe to posts

Untitled

posted Jun 28, 2011, 6:27 AM by Darren Ash   [ updated Jul 1, 2015, 1:59 PM ]



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


UTC Date Time Handling

posted Jun 25, 2011, 12:21 PM by Darren Ash

Great source code on the Code Project website for this.  Works well.  Contains all source material on the site and is free to become a member.  The project source is attached.


The general description of the project is:

Introduction

SQL Server does not provide a simple way to convert a UTC datetime value to a local time value. This project includes a table (tbTimeZoneInfo) with data to provide the Time Zone information and two functions toconvert a UTC datetime value to any Local Time Zone.

The tbTimeZoneInfo table contains the Time Zone information for all the time zones from the registry underHKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones. Anyone familiar with theTIME_ZONE_INFORMATION structure will recognize the structure of table tbTimeZoneInfo.

The function named GetLocalDateTime arguments are a TimeZoneID value from tbTimeZoneInfo and aUTC DateTime value. The GetUTCDate() function can be used to obtain the current UTC date.

Values in the tbTimeZoneInfo can be changed as needed. The values that are supplied in the form of SQLinsertion scripts may not be up to date. They were obtained from the registry at a point in time and may now be out of date.

Simply read the UTCToLocal_ReadMe.txt file for instructions on creating the objects, inserting data and testing the GetLocalDateTime function.

| Attachments: ConvertUTCToLocal_src.zip

1-3 of 3