Migrate SQL Server DateTime column to DateTimeOffset

Question

I have an old table with a few rows that has a datetime column. I want to switch this to datetimeoffset but I want to be able to transfer the data that already exists. So I'm doing something like:

SET IDENTITY_INSERT Table_Temp ON

INSERT INTO Table_Temp
    (Col0, ... ColN,)
SELECT 
    COl0,.... ColN, from 
Table_Original;
    
SET IDENTITY_INSERT Table_Temp OFF

This works but the offset set is 0 when I do the datetime to datetimeoffset assignment. Fortunately the offset that I want to set it to is the offset of the current system. I can't seem to figure out an easy way to do this.

I want to be able to set the offset within the conversion. I was going to resort to doing a C# utility(or PowerShell) but I would rather keep it simple.

Answer 1

If you're using a version of SQL Server that knows of the datetimeoffset type, this syntax will work for getting you the local tz offset of the server:

select datepart(tz,sysdatetimeoffset())

The result is in MINUTES.