SQL Tips

 

[SQL Server] DATETIME データ型のミリ秒に関する注意事項

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」までのデータが取得できました。

ミリ秒について少しは勉強になりましたか?
このエントリーをはてなブックマークに追加
2014-10-01 18:24:15   3111

コメント

[SQL Server] 順位付け関数「RANK」、「DENSE_RANK」について

-.日付:2014-10-15   カテゴリ : -   閲覧数:2191

[SQL Server] DISTINCT - 重複行を除外してデータの取得方法

-.日付:2014-10-03   カテゴリ : -   閲覧数:2888

[SQL Server] DATETIME データ型のミリ秒に関する注意事項

-.日付:2014-10-01   カテゴリ : -   閲覧数:3111

[SQL Server] RAND(ランダム)関数で乱数を取得する方法

-.日付:2014-09-24   カテゴリ : -   閲覧数:11893

[SQL Server] 順位付け関数「ROW_NUMBER」について

-.日付:2014-09-17   カテゴリ : -   閲覧数:2564

[SQL Server] LIKEで「_」、「%」が入った文字を検索する方法

-.日付:2014-09-17   カテゴリ : -   閲覧数:3970

[SQL Server] SQL Server CPU 使用率が高いクエリを検索する方法

-.日付:2014-09-12   カテゴリ : -   閲覧数:7045

[SQL Server] 既存のテーブルにPrimary Key(PK)を設定する方法

-.日付:2014-08-20   カテゴリ : -   閲覧数:2030

[SQL Server] テーブルの一覧を取得する方法 - Table List

-.日付:2014-08-14   カテゴリ : -   閲覧数:2255

Copyright © 2015 INNOYA.COM All rights reserved. RSS