SQL Server - Convert UTC to Local Time

• Download source files - 4.45 KB

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 to convert 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 under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones. Anyone familiar with the TIME_ZONE_INFORMATION structure will recognize the structure of table tbTimeZoneInfo.

The function named GetLocalDateTime arguments are a TimeZoneID value from tbTimeZoneInfo and a UTC 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 SQL insertion 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.

History

• 11^th July, 2006: Initial post