MS-SQL / SQL Server
DATETIME データ型のミリ秒に関する注意事項
DATETIMEデータ型のミリ秒には注意が必要です。
ミリ秒には「.000 ~ .997」までしか存在しないのです。
えぇ？？ どういう意味？
では、もうちょっと詳しく説明したいと思います。
下記の例文を実行し結果を確認しながら説明しましょう。
まずはテストの為にテスト用のテーブルを作成とテスト用のデータを登録してください。
-- #### テスト用テーブル作成
CREATE TABLE dbo.TempData
(
Code int
, RegDate datetime
)
-- #### テスト用データ登録
INSERT INTO dbo.TempData
SELECT 1, '2014-01-01 23:59:59.990' UNION ALL
SELECT 2, '2014-01-01 23:59:59.991' UNION ALL
SELECT 3, '2014-01-01 23:59:59.992' UNION ALL
SELECT 4, '2014-01-01 23:59:59.993' UNION ALL
SELECT 5, '2014-01-01 23:59:59.994' UNION ALL
SELECT 6, '2014-01-01 23:59:59.995' UNION ALL
SELECT 7, '2014-01-01 23:59:59.996' UNION ALL
SELECT 8, '2014-01-01 23:59:59.997' UNION ALL
SELECT 9, '2014-01-01 23:59:59.998' UNION ALL
SELECT 10, '2014-01-01 23:59:59.999' UNION ALL
SELECT 11, '2014-01-02 00:00:00.000' UNION ALL
SELECT 12, '2014-01-02 00:00:00.001' UNION ALL
SELECT 13, '2014-01-02 00:00:00.002' UNION ALL
SELECT 14, '2014-01-02 00:00:00.003' UNION ALL
SELECT 15, '2014-01-02 00:00:00.004' UNION ALL
SELECT 16, '2014-01-02 00:00:00.005'
-- #### テストデータ確認
SELECT * FROM dbo.TempData
実行結果
Code RegDate
----------- -----------------------
1 2014-01-01 23:59:59.990
2 2014-01-01 23:59:59.990
3 2014-01-01 23:59:59.993
4 2014-01-01 23:59:59.993
5 2014-01-01 23:59:59.993
6 2014-01-01 23:59:59.997
7 2014-01-01 23:59:59.997
8 2014-01-01 23:59:59.997
9 2014-01-01 23:59:59.997
10 2014-01-02 00:00:00.000
11 2014-01-02 00:00:00.000
12 2014-01-02 00:00:00.000
13 2014-01-02 00:00:00.003
14 2014-01-02 00:00:00.003
15 2014-01-02 00:00:00.003
16 2014-01-02 00:00:00.007
この結果をよく見てください。
ミリ秒の最後の数字が「0」、「3」、「7」しかありません。
登録する時は「0~9」まで順番どおり登録したのに実際に登録されているデータはちょっと違いますよね。
つまり手動でミリ秒を「0~9」まで作って登録するとデータがそのまま登録されないのです。
データは下記のように変換され登録されます。
| 0 |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
| 0 |
3 |
7 |
0 |
では、どんなところに注意が必要なのか！！
例えば、「2014-01-01」までのデータを抽出する時に下記のように抽出する人もいると思います。
SELECT * FROM dbo.TempData
WHERE RegDate <= '2014-01-01 23:59:59.999'
もしくは
SELECT * FROM dbo.TempData
WHERE RegDate BETWEEN '2014-01-01 00:00:00.000' AND '2014-01-01 23:59:59.999'
このSQLで全然問題なさそうですよね。
では、実行結果を見てみましょう。
実行結果
Code RegDate
----------- -----------------------
1 2014-01-01 23:59:59.990
2 2014-01-01 23:59:59.990
3 2014-01-01 23:59:59.993
4 2014-01-01 23:59:59.993
5 2014-01-01 23:59:59.993
6 2014-01-01 23:59:59.997
7 2014-01-01 23:59:59.997
8 2014-01-01 23:59:59.997
9 2014-01-01 23:59:59.997
10 2014-01-02 00:00:00.000
11 2014-01-02 00:00:00.000
12 2014-01-02 00:00:00.000
よく見てください。
WHERE句には「2014-01-01 23:59:59.999」まで検索したのに、
「2014-01-02 00:00:00.000」まで検索されています。
ミリ秒のデータ変換によって「2014-01-01 23:59:59.999」は「2014-01-02 00:00:00.000」に認識するのです。
では、解決方法は？？
検索する時に「2014-01-01 23:59:59.999」ではなく「2014-01-02 00:00:00.000」で検索するのです。
SELECT * FROM dbo.TempData
WHERE RegDate < '2014-01-02 00:00:00'
もしくはもっと簡単に。。。
SELECT * FROM dbo.TempData
WHERE RegDate < '2014-01-02'
これで実行結果を見ますと～～
実行結果
Code RegDate
----------- -----------------------
1 2014-01-01 23:59:59.990
2 2014-01-01 23:59:59.990
3 2014-01-01 23:59:59.993
4 2014-01-01 23:59:59.993
5 2014-01-01 23:59:59.993
6 2014-01-01 23:59:59.997
7 2014-01-01 23:59:59.997
8 2014-01-01 23:59:59.997
9 2014-01-01 23:59:59.997
どうですか？
「2014-01-01」までのデータが取得できました。
ミリ秒について少しは勉強になりましたか？