問題描述
我有一個(gè)舊表，其中幾行有一個(gè)日期時(shí)間列.我想將其切換到 datetimeoffset 但我希望能夠傳輸已經(jīng)存在的數(shù)據(jù).所以我正在做類似的事情:
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
這有效，但是當(dāng)我執(zhí)行 datetime 到 datetimeoffset 分配時(shí)，偏移量設(shè)置為 0.幸運(yùn)的是，我想設(shè)置的偏移量是當(dāng)前系統(tǒng)的偏移量.我似乎想不出一個(gè)簡單的方法來做到這一點(diǎn).
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.
我希望能夠在轉(zhuǎn)換中設(shè)置偏移量.我打算使用 C# 實(shí)用程序(或 PowerShell)，但我寧愿保持簡單.
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.
推薦答案
如果您使用的 SQL Server 版本知道 datetimeoffset 類型，則此語法將用于獲取服務(wù)器的本地 tz 偏移量:
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:
選擇日期部分(tz,sysdatetimeoffset())
結(jié)果在 MINUTES 內(nèi).
The result is in MINUTES.
這篇關(guān)于將 SQL Server DateTime 列遷移到 DateTimeOffset的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！