Click here to Skip to main content
11,490,408 members (56,997 online)
Click here to Skip to main content

SQL Server - Convert UTC to Local Time

, 11 Jul 2006 CPOL 121.6K 2.2K 31
Rate this:
Please Sign up or sign in to vote.
Convert UTC to local time

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

  • 11th July, 2006: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Chris Tillotson
Web Developer
United States United States
Live in Santa Barbara, CA aka "Paradise" with my awesome wife and daughter. Work as a software developer/typist.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Cesar Esquivel19-Apr-13 10:45
memberCesar Esquivel19-Apr-13 10:45 
QuestionSummary of some issues Pin
emperorwal15-Nov-11 5:27
memberemperorwal15-Nov-11 5:27 
AnswerRe: Summary of some issues Pin
Joseph Leathlean13-Mar-12 7:43
memberJoseph Leathlean13-Mar-12 7:43 
GeneralMy vote of 5 Pin
seenit1-Jul-11 16:18
memberseenit1-Jul-11 16:18 
GeneralIndeed great article Pin
kishor kurapati3-Feb-11 23:54
memberkishor kurapati3-Feb-11 23:54 
GeneralNorthern vs. Southern Hemisphere Pin
Jack Siedliski23-Apr-10 13:23
memberJack Siedliski23-Apr-10 13:23 
QuestionDoes not work? Pin
retro__3-Mar-10 13:14
memberretro__3-Mar-10 13:14 
AnswerRe: Does not work? Pin
retro__3-Mar-10 13:36
memberretro__3-Mar-10 13:36 
GeneralDaylight saving time change for USA Pin
Roman Skrabis17-Dec-09 0:53
memberRoman Skrabis17-Dec-09 0:53 
GeneralRe: Daylight saving time change for USA Pin
Josh Warner-Burke15-Jan-10 9:46
memberJosh Warner-Burke15-Jan-10 9:46 
GeneralRe: Daylight saving time change for USA Pin
AndyTexas15-Nov-10 7:09
memberAndyTexas15-Nov-10 7:09 
GeneralIssue where SQL Server DateFirst not set to 7 Pin
Tim Friesen28-Sep-09 6:21
memberTim Friesen28-Sep-09 6:21 
Generalgreat script Pin
trance_dude28-May-09 11:24
membertrance_dude28-May-09 11:24 
Generalgreat Pin
Ra...aj17-Mar-09 2:11
memberRa...aj17-Mar-09 2:11 
General"archive file is damaged" error Pin
stephen myint4-Dec-08 18:57
memberstephen myint4-Dec-08 18:57 
GeneralThanks for solving a problem that should be handled by the DB Pin
Stephen W Giles17-Oct-08 8:27
memberStephen W Giles17-Oct-08 8:27 
GeneralRe: Thanks for solving a problem that should be handled by the DB Pin
buckindb3-Nov-08 5:34
memberbuckindb3-Nov-08 5:34 
GeneralRe: Thanks for solving a problem that should be handled by the DB Pin
Stephen W Giles4-Nov-08 4:05
memberStephen W Giles4-Nov-08 4:05 
GeneralRe: Thanks for solving a problem that should be handled by the DB Pin
Tim Friesen28-Sep-09 4:06
memberTim Friesen28-Sep-09 4:06 
GeneralRe: Thanks for solving a problem that should be handled by the DB Pin
Tim Friesen28-Sep-09 4:34
memberTim Friesen28-Sep-09 4:34 
GeneralConvert from local to UTC Pin
tomasz1234526-Oct-07 9:29
membertomasz1234526-Oct-07 9:29 
GeneralBug in GetDaylightStandardDateTime Pin
rwt2sdf527-Sep-07 5:01
memberrwt2sdf527-Sep-07 5:01 
QuestionHi , Some Proble Pin
Ravi KM Mishra13-Sep-07 19:28
memberRavi KM Mishra13-Sep-07 19:28 
GeneralSmall problem with GetLocalDateTime Pin
rockyfan2067-Dec-06 18:15
memberrockyfan2067-Dec-06 18:15 
GeneralResponse to question about returning local date only Pin
Chris Tillotson24-Jul-06 11:10
memberChris Tillotson24-Jul-06 11:10 
GeneralThis is great!! One question Pin
mingo336924-Jul-06 9:25
membermingo336924-Jul-06 9:25 
GeneralLink to file is broken. Pin
Marc Leger11-Jul-06 17:04
memberMarc Leger11-Jul-06 17:04 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150520.1 | Last Updated 11 Jul 2006
Article Copyright 2006 by Chris Tillotson
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid