SQL Server中datetimeset轉換datetime型別問題淺析-有解無憂

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

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/241824.html

標籤：SQL Server

上一篇：Cassandra與Kubernetes

下一篇：Cassandra與Kubernetes