How to migrate DateTime values to DateTimeOffset in SQL Server?

Question

Given a SQL table with a DateTime column and approx 100k rows of various dates (local time PST values) from the past 3 years, what is the best strategy to migrate those column values to DateTimeOffset to "add" the missing utc tz offset info?

The existing DateTime values have been stored without any time zone/utc offset details. The dates stored are always representative of Pacific Time (-800 or -700 depending on Daylight Saving Time). Goal is to retroactively add the tz offset to all existing data with the assumption that the date came from Pacific time (whatever the correct offset was at the moment specified by the date)

• Within SQL what is the best practice for this type of migration without losing any data, or changing existing values?

• Taking it to the next step, what is the most efficient method to migrate an entire database of a moderate size (~100gb in ~100 tables with 2 DateTime columns per table) to use DateTimeOffset columns and values?

• What happens to the datetimes that were logged around/during 2am on the PST/PDT changeover date? Is there a data loss that happens?

SQL Server 2008 + C# 4.5

If this is not the right area please point me in the right direction, thanks!

Edit: Yay, bounty time.

Answer 1

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.)