SQL Server How to persist and use a time across different time zones

In SQL Server I would like to create a table to save time开发者_StackOverflow社区 of an event, and would like to convert it into the timezone of the users choice for display purposes. Let us say that If there was an event that happens in London at 1:00 PM GMT, that would be 8:00 am US EST.

Given This example I would like to create a frame work,

1. where a user would have an ability to save the event and time (Giving the time zone of the event)

2. Read Those events, with the time displayed in the time zone of his liking (US EST)

How do I accomplish this in SQL Server.

In SQL Server 2008, use the DATETIMEOFFSET data type which is a DATETIME plus a timezone offset included.

SELECT CAST('2010-11-23 16:35:29+09:00' AS datetimeoffset) 

would be Nov 23, 2010, 4:35pm in a +9 hour (from GMT) timezone.

SQL Server 2008 also contains functions and SQL commands to convert DATETIMEOFFSET values from one timezone to another:

SELECT 
SWITCHOFFSET(CAST('2010-11-23 16:35:29+09:00' AS datetimeoffset), '+01:00')

would result in:

2010-11-23 08:35:29.0000000 +01:00

Same time, different timezone (+1 hour from GMT)

1. When you save the data, save the GMT, not the local time for the user (in c# this is DateTime.UtcNow)
2. In your application logic, record the user's timezone, and translate the GMT time to the user's local time using the timezone offset, at runtime.

The way I've solved a similar problem is to do the following:

1. The table design is to only store GMT time.
2. All input goes through a stored proc that requires an input of a timezone offset.
3. The data request is to a Table-Valued Function, with an input for the timezone offset.