快捷導(dǎo)航

解析SQL Server中datetimeset轉(zhuǎn)換datetime類型問(wèn)題

更新時(shí)間：2020年12月28日 17:14:30 作者：瀟湘隱者

這篇文章主要介紹了SQL Server中datetimeset轉(zhuǎn)換datetime類型問(wèn)題淺析,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下

在SQL Server中，數(shù)據(jù)類型datetimeoffset轉(zhuǎn)換為datetime類型或datetime2類型時(shí)需要特別注意，有可能一不小心你可能會(huì)碰到下面這種情況。下面我們構(gòu)造一個(gè)簡(jiǎn)單案例，模擬一下你們可能遇到的情況。

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;

如下截圖所示，你會(huì)發(fā)現(xiàn)這個(gè)查詢SQL查不到任何記錄。相信以前對(duì)數(shù)據(jù)類型datetimeoffset不太熟悉的人會(huì)對(duì)這個(gè)現(xiàn)象一臉懵逼......

那么我們通過(guò)下面例子來(lái)給你簡(jiǎn)單介紹一下，datetimeoffset通過(guò)不同方式轉(zhuǎn)換為datetime有啥區(qū)別，具體腳本如下：

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'

如下截圖所示，通過(guò)CONVERT函數(shù)將datetiemoffset轉(zhuǎn)換為datetime,你會(huì)發(fā)現(xiàn)上面這種方式丟失了時(shí)區(qū)信息，它將datetimeoffset轉(zhuǎn)換為了UTC時(shí)間了。官方文檔介紹：轉(zhuǎn)換到datetime 時(shí)，會(huì)復(fù)制日期和時(shí)間值，時(shí)區(qū)被截?cái)唷?/p>

注意：datetiemoffset轉(zhuǎn)換為datetime2也是同樣的情況，這里不做贅述了。

所以，最開(kāi)始，我們構(gòu)造的案例中，出現(xiàn)那種現(xiàn)象是因?yàn)锧p1和CREATE_TIME比較時(shí)，發(fā)生了隱式轉(zhuǎn)換，datetiemoffset轉(zhuǎn)換為datetime，而且轉(zhuǎn)換過(guò)程中時(shí)區(qū)丟失了，此時(shí)的SQL實(shí)際等價(jià)于CREATE_TIME <='2020-10-03 03:10:36.920'了，那么怎么解決這個(gè)問(wèn)題，如果在不改變數(shù)據(jù)類型的情況下，有什么解決方案解決這個(gè)問(wèn)題呢？

方案1：使用CAST轉(zhuǎn)換函數(shù)。

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函數(shù)中指定date_style為0 ，可以保留時(shí)區(qū)信息。

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)

下面例子演示對(duì)比，有興趣的話，自行執(zhí)行SQL后對(duì)比觀察

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'

方案3：SQL Server 2016（13.x）或以后的版本可以使用下面方案。

注意之前的SQL Server版本不支持這種寫(xiě)法.

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

到此這篇關(guān)于SQL Server中datetimeset轉(zhuǎn)換datetime類型問(wèn)題淺析的文章就介紹到這了,更多相關(guān)SQL Server中datetimeset轉(zhuǎn)換datetime類型內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: