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 .