SQL Server中datetimeset轉換datetime型別問題淺析

瀟湘隱者發表於2020-12-28

在SQL Server中,資料型別datetimeoffset轉換為datetime型別或datetime2型別時需要特別注意,有可能一不小心你可能會碰到下面這種情況。下面我們構造一個簡單案例,模擬一下你們可能遇到的情況。

 

CREATE TABLE TEST
(
    ID                 INT IDENTITY(1,1) 
   ,CREATE_TIME        DATETIME
   ,CONSTRAINT PK_TEST PRIMARY KEY(ID)
 
);
GO
 
INSERT INTO TEST(CREATE_TIME)
SELECT '2020-10-03 11:10:36' UNION ALL
SELECT '2020-10-03 11:11:36' UNION ALL
SELECT '2020-10-03 11:12:36' UNION ALL
SELECT '2020-10-03 11:13:36';
 
DECLARE @p1 DATETIMEOFFSET;
SET @p1='2020-10-03 11:12:36.9200000 +08:00'
SELECT * FROM dbo.TEST
WHERE CREATE_TIME <=@p1;

 

如下截圖所示,你會發現這個查詢SQL查不到任何記錄。相信以前對資料型別datetimeoffset不太熟悉的人會對這個現象一臉懵逼......

 

clip_image001

 

那麼我們通過下面例子來給你簡單介紹一下,datetimeoffset通過不同方式轉換為datetime有啥區別,具體指令碼如下: 

DECLARE @p1 DATETIMEOFFSET;
DECLARE @p2 DATETIME;
DECLARE @p3 DATETIME2;
 
SET @p1='2020-10-03 11:10:36.9200000 +08:00'
SET @p2=@p1;
SET @p3=@p1;
 
SELECT @p1                             AS '@p1'
     ,@p2                              AS '@p2'
     ,CAST(@p1 AS DATETIME)            AS 'datetimeoffset_cast_datetime'
     ,CONVERT(DATETIME, @p1, 1)        AS 'datetimeoffset_convert_datetime'

 

如下截圖所示,通過CONVERT函式將datetiemoffset轉換為datetime,你會發現上面這種方式丟失了時區資訊,它將datetimeoffset轉換為了UTC時間了。官方文件介紹:轉換到datetime 時,會複製日期和時間值,時區被截斷。 

 

注意datetiemoffset轉換為datetime2也是同樣的情況,這裡不做贅述了。

clip_image002

 

所以,最開始,我們構造的案例中,出現那種現象是因為@p1和CREATE_TIME比較時,發生了隱式轉換,datetiemoffset轉換為datetime,而且轉換過程中時區丟失了,此時的SQL實際等價於CREATE_TIME <='2020-10-03 03:10:36.920'了,那麼怎麼解決這個問題,如果在不改變資料型別的情況下,有什麼解決方案解決這個問題呢?

 

方案1:使用CAST轉換函式。

 

DECLARE @p1 DATETIMEOFFSET;
SET @p1='2020-10-03 11:12:36.9200000 +08:00'
SELECT * FROM dbo.TEST
WHERE CREATE_TIME <=CAST(@p1 AS DATETIME)

 

方案2:CONVERT函式中指定date_style為0 ,可以保留時區資訊。

 

DECLARE @p1 DATETIMEOFFSET;
SET @p1='2020-10-03 11:12:36.9200000 +08:00'
SELECT * FROM dbo.TEST
WHERE CREATE_TIME <=CONVERT(DATETIME, @p1, 0)

 

下面例子演示對比,有興趣的話,自行執行SQL後對比觀察

 

DECLARE @p1 DATETIMEOFFSET;
DECLARE @p2 DATETIME;
DECLARE @p3 DATETIME2;
 
SET @p1='2020-10-03 11:10:36.9200000 +08:00'
SET @p2=@p1;
SET @p3=@p1;
 
SELECT @p1                             AS '@p1'
     ,@p2                              AS '@p2'
     ,CAST(@p1 AS DATETIME)            AS 'datetimeoffset_cast_datetime'
     ,CONVERT(DATETIME, @p1, 0)        AS 'datetimeoffset_convert_datetime'
     ,CONVERT(DATETIME, @p1, 1)        AS 'datetimeoffset_convert_datetime1'

 

clip_image003

 

方案3:SQL Server 2016(13.x)或以後的版本可以使用下面方案。注意之前的SQL Server版本不支援這種寫法.

 

DECLARE @p1 DATETIMEOFFSET;
SET @p1='2020-10-03 11:12:36.9200000 +08:00'
SELECT * FROM dbo.TEST
WHERE CREATE_TIME <= CONVERT(DATETIME, @p1 AT TIME ZONE 'UTC' AT TIME ZONE 'China Standard Time')

相關文章