Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationEventAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
21
Help us understand the problem. What are the problem?

More than 1 year has passed since last update.

posted at

Organization

【SQL Server】DATETIME型の落とし穴

概要

SQL ServerのDATETIME型ではミリ秒が丸め込みされてしまうのでその事象とDATETIME型より細かい単位で日時を判定できる型が存在するので紹介します!

きっかけ

日付型の値を見ているとなぜか定義した値と違う値になっていることがあって躓いたのがきっかけでした。

declare @datetime_0 datetime = '2020-01-01 23:59:59.990'
select @datetime_0

-- 実行結果は 2020-01-01 23:59:59.990 になっている

declare @datetime_1 datetime = '2020-01-01 23:59:59.991'
select @datetime_1

-- 実行結果は 2020-01-01 23:59:59.990 になっている

declare @datetime_2 datetime = '2020-01-01 23:59:59.992'
select @datetime_2

-- 実行結果は 2020-01-01 23:59:59.993 になっている

declare @datetime_3 datetime = '2020-01-01 23:59:59.993'
select @datetime_3

-- 実行結果は 2020-01-01 23:59:59.993 になっている

declare @datetime_4 datetime = '2020-01-01 23:59:59.994'
select @datetime_4

-- 実行結果は 2020-01-01 23:59:59.993 になっている

declare @datetime_5 datetime = '2020-01-01 23:59:59.995'
select @datetime_5

-- 実行結果は 2020-01-01 23:59:59.997 になっている

declare @datetime_6 datetime = '2020-01-01 23:59:59.996'
select @datetime_6

-- 実行結果は 2020-01-01 23:59:59.997 になっている

declare @datetime_7 datetime = '2020-01-01 23:59:59.997'
select @datetime_7

-- 実行結果は 2020-01-01 23:59:59.997 になっている

declare @datetime_8 datetime = '2020-01-01 23:59:59.998'
select @datetime_8

-- 実行結果は 2020-01-01 23:59:59.997 になっている

declare @datetime_9 datetime = '2020-01-01 23:59:59.999'
select @datetime_9

-- 実行結果は 2020-01-02 00:00:00.000 になっている

上記のように、あれ???定義したミリ秒と違う値で取得されてる???という疑問が生まれました。
後々調べて自分の知識不足だったのですが、今まで気づかなかったので記事にしてみます:sweat_smile:

なぜ定義したミリ秒とは異なる値になっている???

Microsoftの公式のDATETIME型のリファレンスによると、

時間の範囲 : 00:00:00 から 23:59:59.997
精度 : 値は、.000、.003、または .007 秒単位に丸められます。

ということらしい!なるほど!
↑の実行結果からもわかるように以下のように丸め込みが行われているみたいです!

.xx0 ~ .xx1 : .xx0 に丸め込み
.xx2 ~ .xx4 : .xx3 に丸め込み
.xx5 ~ .xx8 : .xx7 に丸め込み
.xx9 : .xx0 に繰り上げ

もっと精度が細かい日付型

なにやらDATETIME2という、もっと精度が細かくて丸め込みも勝手にされない日付型があるらしい!!!

時間の範囲 : 00:00:00 から 23:59:59.9999999
精度 : 100 ナノ秒

これを使えば丸め込みされず、もっと正確な判断ができる!
というわけでDATETIME2型で定義した日時を確認してみてください!

declare @datetime2_0 datetime2 = '2020-01-01 23:59:59.990'
select @datetime2_0

declare @datetime2_1 datetime2 = '2020-01-01 23:59:59.991'
select @datetime2_1

declare @datetime2_2 datetime2 = '2020-01-01 23:59:59.992'
select @datetime2_2

declare @datetime2_3 datetime2 = '2020-01-01 23:59:59.993'
select @datetime2_3

declare @datetime2_4 datetime2 = '2020-01-01 23:59:59.994'
select @datetime2_4

declare @datetime2_5 datetime2 = '2020-01-01 23:59:59.995'
select @datetime2_5

declare @datetime2_6 datetime2 = '2020-01-01 23:59:59.996'
select @datetime2_6

declare @datetime2_7 datetime2 = '2020-01-01 23:59:59.997'
select @datetime2_7

declare @datetime2_8 datetime2 = '2020-01-01 23:59:59.998'
select @datetime2_8

declare @datetime2_9 datetime2 = '2020-01-01 23:59:59.999'
select @datetime2_9

このようにミリ秒・ナノ秒まで考慮するようなシステムを作るときはDATETIME2型を検討してみてはいかがでしょうか!

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
21
Help us understand the problem. What are the problem?