How to migrate DateTime values to DateTimeOffset in SQL Server?

Daylight saving isn't always an exact science. E.g. there was a rule change in 2007 for the period used in North America. For this reason, would suggest populating a table with the relevant dates. E.g. for 2000-2013:

-- Populate a table with PST daylight saving start/end times
-- Example data for 2000-2013 - sourced from
-- http://www.timeanddate.com/worldclock/timezone.html?n=137
CREATE TABLE dst (start DateTime, [end] DateTime);
INSERT INTO dst (start, [end]) VALUES ('02:00 2 Apr 2000', '02:00 29 Oct 2000');
INSERT INTO dst (start, [end]) VALUES ('02:00 1 Apr 2001', '02:00 28 Oct 2001');
INSERT INTO dst (start, [end]) VALUES ('02:00 7 Apr 2002', '02:00 27 Oct 2002');
INSERT INTO dst (start, [end]) VALUES ('02:00 6 Apr 2003', '02:00 26 Oct 2003');
INSERT INTO dst (start, [end]) VALUES ('02:00 4 Apr 2004', '02:00 31 Oct 2004');
INSERT INTO dst (start, [end]) VALUES ('02:00 3 Apr 2005', '02:00 30 Oct 2005');
INSERT INTO dst (start, [end]) VALUES ('02:00 2 Apr 2006', '02:00 29 Oct 2006');
INSERT INTO dst (start, [end]) VALUES ('02:00 11 Apr 2007', '02:00 4 Oct 2007');
INSERT INTO dst (start, [end]) VALUES ('02:00 9 Apr 2008', '02:00 2 Nov 2008');
INSERT INTO dst (start, [end]) VALUES ('02:00 8 Apr 2009', '02:00 1 Nov 2009');
INSERT INTO dst (start, [end]) VALUES ('02:00 14 Apr 2010', '02:00 7 Nov 2010');
INSERT INTO dst (start, [end]) VALUES ('02:00 13 Apr 2011', '02:00 6 Nov 2011');
INSERT INTO dst (start, [end]) VALUES ('02:00 11 Apr 2012', '02:00 4 Nov 2012');
INSERT INTO dst (start, [end]) VALUES ('02:00 10 Apr 2013', '02:00 3 Nov 2013');

Of course you may need a lot more than this - will leave doing that as an exercise for the reader :-)

OK, so let's say you populated the above table with the whole possible range and you have date/times in field dt of table test. Then you can join to the above table and do the conversions like this:

-- Convert sample dates to PST offset with daylight saving where appropriate
SELECT test.dt,
       CAST(CONVERT(VARCHAR(23), test.dt, 126) + -- Convert to ISO8601 format
            CASE WHEN dst.start IS NULL
                 THEN '-08:00' -- No record joined so not within DST period
                 ELSE '-07:00' -- DST record joined so is within DST period
            END AS DateTimeOffset) AS dto
FROM test
LEFT JOIN dst -- Join on daylight savings table to find out whether DST applies
ON test.dt >= dst.start
AND test.dt <= dst.[end]

Here's an SQL fiddle demo.

SQL Server 2016 introduced the T-SQL syntax AT TIME ZONE to solve these kind of problems.

So if you know the time zone of the data, you can append the offset using a simple alter and update script as shown below (assuming you have a table named MyTable having a datetime2 column named DateTimeColumn):

alter table MyTable 
   alter column DateTimeColumn datetimeoffset;

update Mytable
   set DateTimeColumn = convert(datetime2, DateTimeColumn) AT TIME ZONE 'Pacific Standard Time' 

Daylight Saving Time (DST) is considered and there should be no data loss.

A list of installed time zones can be retrieved by querying the sys.time_zone_info view.

(I know the OP specified SQL Server 2008 and the question was asked and answered a long time ago, but hopefully anyone currently struggling with the same kind of problem will be helped by this answer.)