2019年10月30日

SQLServerでDATETIME型を使用する際の注意点

今回はSQLServerの「DATETIME型」について書いていきたいと思います。

 

なぜDATETIME型について書こうと思ったかといいますと・・・
ある期間のデータを取得したいと思いSQL文を作成したところ、当たり前に取得できると思っていたデータがなぜか取得されず…
なぜ取得されなかったか調査をしましたところ、SQLServerの仕様をきちんと把握していなければ気づけない内容だったからです。

 

まずは、INSERTを行った場合からみていきましょう。

 

カラムにID（test_id）と日付（test_date）だけ用意した「test_tbl_01」に対して、以下のSQLでデータの挿入を行ってみます。
ちなみに日付はもちろん「DATETIME型」としています。

INSERT INTO test_tbl_01 VALUES ( '001' , '2019-08-26 18:12:44.413' );
INSERT INTO test_tbl_01 VALUES ('002','2019-08-26 18:13:50.214');
INSERT INTO test_tbl_01 VALUES ('003','2019-08-26 18:15:32.001');
INSERT INTO test_tbl_01 VALUES ('004','2019-08-26 18:15:44.552');
INSERT INTO test_tbl_01 VALUES ('005','2019-08-26 18:16:01.012');
INSERT INTO test_tbl_01 VALUES ('006','2019-08-26 18:18:18.368');
INSERT INTO test_tbl_01 VALUES ('007','2019-08-26 18:19:21.322');
INSERT INTO test_tbl_01 VALUES ('008','2019-08-26 18:19:35.402');
INSERT INTO test_tbl_01 VALUES ('009','2019-08-26 18:20:09.155');
INSERT INTO test_tbl_01 VALUES ('010','2019-08-26 18:21:10.456');

 

このSQL文を実行したとき、どのように登録されるのでしょうか？
私は何も変化することなく、そのまま登録されるのだと思い込んでいました。

 

そして、実際に登録したデータをみてみると以下のようになっていました。

 

よく見てみると、1行目のデータ以外はミリ秒の3桁目が変わってしまっているのです。
これはどうしてなのか…マイクロソフトのドキュメントを見てみると、このように書いていました。
Microsoft SQLドキュメント：datetime (Transact-SQL)

 

精度…値は、.000、.003、または .007 秒単位に丸められます。

 

この説明を読んだうえで先ほどのデータを確認してみますと、

ミリ秒の3桁目は説明の通り、”0″・”3″・”7″に変換されていますね。

ということで、.000、.003、.007以外が入った場合には.000、.003、.007 に丸められるということが分かりました！

 

私は初めてこれを発見したとき、「データが勝手に変わってしまっている！？」ととても驚いたものです…

SQLServerを当たり前のように使用している方にはきっと当たり前のことなのでしょう…

 

続いて、SELECTを行った場合について。

こちらは作成するSQLによって取得結果が変わってしまうため要注意です。

 

上記のデータから以下のSQL文を使って、2019/8/26分のみのデータを取得したいと思います。

 

SELECT * FROM test_tbl_01
WHERE test_date BETWEEN '2019-08-26 00:00:00.000' AND '2019-08-26 23:59:59.999'

 

結果はこのようになります。

 

WHERE条件を「’2019-08-26 00:00:00.000′ AND ‘2019-08-26 23:59:59.999’」にしてしまうと、

赤枠で示している通り、test_dateが”2019-08-27 00:00:00.000″のデータも含まれてしまうんです…

 

まさにこれが私が SQLServer DATETIME型 に悩まされた最大の理由となります。

 

では、なぜ”2019-08-27 00:00:00.000″のデータまで含まれてしまうのでしょう？

その理由は、DATETIME型の時間範囲が「00:00:00 から 23:59:59.997」であるからです。

 

INSERTの場合でも説明した通り、.000、.003、.007以外が入ると丸められてしまうということは
WHERE条件の中の”2019-08-26 23:59:59.999″は”2019-08-27 00:00:00″に変換されてSELECT文を実行するため、
“2019-08-27 00:00:00″も取得されてしまうのです。

 

2019/8/26分のみのデータを取得したい場合にはSQL文を以下の通りに変更をします。
 

SELECT * FROM test_tbl_01
WHERE test_date BETWEEN '2019-08-26 00:00:00.000' AND '2019-08-26 23:59:59.997'

 

実行結果：

test_id=010以降の2019/8/27分のデータは取得せず、2019/8/26分のみ取得できるようになりました。

その他、datetime2型 datetimeoffset型 を使用すれば、”2019-08-26 23:59:59.999″としても取得できそうですね。

Microsoft SQLドキュメント：日付/時刻型

 

SQL文で思うように操作は可能ですが、テーブル定義を作成する際にデータ型は問題ないかドキュメントをきちんと確認し、決定することも重要だと感じました。