CODEWITHSUNDEEP
sqldatetimetimezone
Raj
Raj

Reputation: 483

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 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.

Upvotes: 2

Views: 3322

Answers (3)

marc_s
marc_s

Reputation: 754618

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)

Upvotes: 4

chezy525
chezy525

Reputation: 4174

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.

Upvotes: 0

Yaakov Ellis
Yaakov Ellis

Reputation: 41510

  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.

Upvotes: 0

Related Questions