Take the 2-minute tour ×
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. It's 100% free, no registration required.

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): http://blog.sqlauthority.com/2010/07/15/sql-server-datetime-function-switchoffset-example/

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?

Thanks.

share|improve this question
1  
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
1  
...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
add comment

1 Answer 1

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
add comment

Your Answer

 
discard

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.