SQL Serverでdatetimeset変換datetimeタイプの問題を解析します。

SQL Serverでは、データタイプがdatetimeoffsetからdatetimeタイプまたはdatetime 2タイプに変換される場合には、特に注意が必要です。このような状況に遭遇する可能性があります。簡単なケースを作って、あなたたちが出会う可能性のある状況をシミュレーションします。

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にあまり詳しくない人がこの現象に対して無頓着であると信じています。

以下の例を通して簡単に紹介しますが、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'

以下のスクリーンショットに示すように、CONVRT関数によってdatetiems offsetをdatetimeに変換すると、上記のようにタイムゾーン情報が失われていることが分かります。datetimeoffsetをUTC時間に変換しました。公式文書によると、datetimeに変換すると日付と時刻がコピーされ、タイムゾーンが切断されます。
なお、datetiems offsetをdatetime 2に変換するのも同様であり、ここでは詳しくは説明しない。

ですから、最初に私たちが作ったケースでは、@p 1とCREATE_TIME比較時に、暗黙的な変換が発生し、datetiems offsetがdatetimeに変換され、また変換中にタイムゾーンが失われました。この時のSQLは実際にCREATE_に相当します。TIME<='2020-10-03:10:36.90'はこの問題をどう解決しますか？データの種類を変えないで、この問題を解決しますか？
シナリオ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'

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

ここでSQL Serverのdatetimeset変換datetimeタイプについての簡単な文章を紹介します。SQL Serverのdatetimeset変換datetimeタイプの内容は以前の文章を検索したり、以下の関連記事を見たりしてください。これからもよろしくお願いします。