Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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: 1369 | Views in the last 30 days: 2
 
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