Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is there any way to set the timeoffset of a datetimeoffset column directly without having to use SWITCHOFFSET which changes the date as well?

In the post below, we can see the SWITCHOFFSET will change the date accordingly along with setting the timeoffset (which is not what we want):

For example I have the date value as '2013-02-10' and want to record it in different timezones, without losing the '2013-02-10'. Just set the timeoffset to what needed , e.g: +11, -4, -6, etc.

Is it possible?


share|improve this question
If the limitations of datetimeoffset are problematic for you, do what I do: store UTC data and store tz info elsewhere. – Aaron Bertrand Feb 20 '13 at 0:54
...or don't store tz info elsewhere at all, just convert at query time. – Aaron Bertrand Feb 20 '13 at 1:21
Well, I totally agree and understand your point to do it in presentation, but it is a requirement for our scenario, so I should do it in database. – Sky Feb 20 '13 at 5:02

To avoid the extra DateAdd (may be difficult if time zone is a variable), use:

TODATETIMEOFFSET(@datetimeoffset, @timeZone)
share|improve this answer
up vote 0 down vote accepted

We ended up to achieve this by applying the time differs between the original value and the value after applying the SwitchoffSet, and then run the SwitchOffSet to create the Time zone offset:

set @datetime = '2013-02-07 14:28:10.123'
set @datetimeoffset = '2013-02-07 14:28:10.123' 

SELECT @datetimeoffset = SWITCHOFFSET (DateAdd(hh, -10, @datetimeoffset), '+10:00')
SELECT @datetimeoffset AS '@datetimeoffset ', @datetime AS 'datetime';

As you can see, the original date value is unchanged.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.