2

In SQL Server I would like to create a table to save time 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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Raj
  • 473
  • 6
  • 8
  • 1
    Which **version** of SQL Server?? SQL Server 2008 has a new data type `DATETIMEOFFSET` which is a DATETIME including a timezone offset - would be perfect for your case – marc_s Nov 23 '10 at 15:19

3 Answers3

4

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)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
0
  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.
Yaakov Ellis
  • 40,752
  • 27
  • 129
  • 174
0

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.
chezy525
  • 4,025
  • 6
  • 28
  • 41
  • FYI: `GMT` is, in programmer-speak, deprecated. It is based **solar day averages**. `UTC` is based on the atomic clock and replaces `GMT`. For the purposes stated here, they are equivalent, however `GMT` is falling out of use. http://geography.about.com/od/timeandtimezones/a/gmtutc.htm – Brad Nov 23 '10 at 16:09