Migrate SQL Server DateTime column to DateTimeOffs

问题:

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 dattime to datetimeoffset assignment. Fortunately the offset that i want to set it to is the offset of the current system. I'm no tsql guru but 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.

回答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.

回答2:

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');

回答3:

These conversions functions will not work correctly if DST saving is active in the target timezone, as the timezone offset changes inside the same year.

回答4:

You can figure out what the offset of the current SQL server is using the following.

select datediff(MI,getdate(), getutcdate())

You need to get the offset in minutes and not hours since there are a number of half hour and even a quarter hour time zone.

Using the minutes value, you can alter your values going in (assuming they were historically all recorded as local time) by using something like

select dateadd(mi,datediff(MI,getdate(), getutcdate()), yourDateField)

For efficiency I would calculate it once into a variable and use that, since the difference is not going to change.

回答5:

For anyone trying to solve this problem correctly, accounting for DST here is the tool to do it.

https://github.com/mj1856/SqlServerTimeZoneSupport

回答6:

This is a slight variation of an answer already provided, and does NOT account for DST changes. However, it might be good enough for many purposes:

dateadd(minute, -datepart(tz, sysdatetimeoffset()), @legacyDatetime)