See below for doc, you probably want something like:
-- up here set the @time_zone variable.
INSERT INTO Table_Temp
(Col0, ... ColN,)
SELECT
COl0, TODATETIMEOFFSET(COLDATE, @time_zone),.... ColN, from
Table_Original;
From MSDN
The SWITCHOFFSET function adjusts an
input DATETIMEOFFSET value to a
specified time zone, while preserving
the UTC value. The syntax is
SWITCHOFFSET(datetimeoffset_value,
time_zone). For example, the following
code adjusts the current system
datetimeoffset value to time zone GMT
+05:00:
SELECT
SWITCHOFFSET(SYSDATETIMEOFFSET(),
'-05:00');
So if the current system
datetimeoffset value is February 12,
2009 10:00:00.0000000 -08:00, this
code returns the value February 12,
2009 13:00:00.0000000 -05:00.
The TODATETIMEOFFSET function sets the
time zone offset of an input date and
time value. Its syntax is
TODATETIMEOFFSET(date_and_time_value,
time_zone).
This function is different from
SWITCHOFFSET in several ways. First,
it is not restricted to a
datetimeoffset value as input; rather
it accepts any date and time data
type. Second, it does not try to
adjust the time based on the time zone
difference between the source value
and the specified time zone but
instead simply returns the input date
and time value with the specified time
zone as a datetimeoffset value.
The main purpose of the
TODATETIMEOFFSET function is to
convert types that are not time zone
aware to DATETIMEOFFSET by the given
time zone offset. If the given date
and time value is a DATETIMEOFFSET,
the TODATETIMEOFFSET function changes
the DATETIMEOFFSET value based on the
same original local date and time
value plus the new given time zone
offset.
For example, the current system
datetimeoffset value is February 12,
2009 10:00:00.0000000 -08:00, and you
run the following code:
SELECT
TODATETIMEOFFSET(SYSDATETIMEOFFSET(),
'-05:00');
The value February 12, 2009
10:00:00.0000000 -05:00 is returned.
Remember that the SWITCHOFFSET
function returned February 12, 2009
13:00:00.0000000 -05:00 because it
adjusted the time based on the time
zone differences between the input
(-08:00) and the specified time zone
(-05:00).
As mentioned earlier, you can use the
TODATETIMEOFFSET function with any
date and time data type as input. For
example, the following code takes the
current system date and time value and
returns it as a datetimeoffset value
with a time zone -00:05:
SELECT TODATETIMEOFFSET(SYSDATETIME(),
'-05:00');