Migrate SQL Server DateTime column to DateTimeOffset
datetimeoffset, sql, sql-server, sql-server-2008, t-sql
Solution
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.
Problem
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.