Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Convert GMT/UTC to Local datetime

By Ritesh Poojara,

Almost all applications enable for internationalization save the datetime columns in UTC/GMT time zones and present convered datetime to local time zones based on users time zone preferences.

Sql server at present does not have any build in functions to be able to do this. To do this type of datetime conversion you need to have all the offset information and the daylight saving information for the timezones. This can be in form of a table where you save timezone and offset with GMT and daylight savings start and end time. There are few changes to the daylight savings after Energy Policy Act 2005. So you need to consider those and any other chagnes as well. I bumped into this problem once and here is how I solved it.

 

This script includes a SQL function dbo.get_local_datetime (datetime, time_zone) to convert GMT based datetime values in the database to local timezone. SQL Function relies on a table containing time zone offsets by time zone ids. The table is created and populated by timezone_offsets.sql.

 

There are different opinions on how many time zones a business application should offer. For example, Microsoft Windows lists all of the major time zones, which is about 74 of them. While this is set as a standard by Microsoft, many applications need to support subsets of these time zones. Java covers a wide range of time zone subsets based on major cities over the globe.

 

This table generated by timezone_offsets.sql covers 599 subsets of all the timezones supported by JDK 6.0. You may require to have data for many more years then what I have given in the script. Due to script size limitation I've added timezone information only for year 2009. You can use a jar application at http://www.codeproject.com/KB/database/GMT_to_local_datetime.aspx or at http://www.codeguru.com/java/article.php/c15981 to genearet timezone offset info for years. So if next java version adds 200 more sub time zones all you need to do is re-genarate this Script .

Total article views: 1251 | Views in the last 30 days: 10
 
Related Articles
FORUM

Knowledge sharing: Date conversion according to TimeZone

Date conversion according to TimeZone

FORUM

Issue converting datetime data in the ISO8601 format with timezone.

Issue converting datetime data in the ISO8601 format with timezone.

FORUM

Timezone conversions using Microsoft timezones.

Has anyone been able to convert time from a given timezone using the Microsoft format like "(GMT-06:...

FORUM

Something clever with TimeZones and GMT offset?

Guys, we're trying to ascertain the GMT off set for a particular country and or state and then be ab...

FORUM

64K Offset

64K Offset

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones