How to set the timeoffset in a Datetimeoffset column without changing the date part

Question

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.

Answer 1

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.