はじめに
数か月前、経験豊かなデータベース管理者兼データベースプログラマが私に質問してきました。Microsoft SQL Serverの日付/時刻関数を使わずに、例えばGETDATE() + 10のような演算式を使って日付/時刻データを操作するうまいやり方を知らないかというのです。そのとき私は、まさに同じ件を話題にしていたインターネットフォーラムのディスカッションを思い出しました。ディスカッションの参加者は皆、Microsoftがこの便利な機能を実装し損なったという事実にうんざりしていました。そこで、私はこのトピックについてもっと掘り下げようと決意しました。その結果がこの記事です。
計算の仕組み
SQL ServerのDATEADD()関数を使って、次の例を実行してみてください。
SELECT GETDATE(); SELECT DATEADD(dd, 1, GETDATE()); SELECT GETDATE() + 1; Results: 2007-03-15 16:21:41.630 2007-03-16 16:21:41.630 2007-03-16 16:21:41.630
ご覧の通り、DATEADD()関数を使っても、プラス記号（+）を使って加算しても、結果はまったく同じです。つまり、現在の日付と時刻に1日が加算されています。DATEADD()でこの結果が得られるのは予想できるでしょう。しかし、加算演算でも同じ結果になるのはなぜでしょうか？ この答えを知るには、SQL Serverの日付/時刻の内部的な格納方法を詳しく調べる必要があります。
ご存知のように、datetimeデータ型は8バイトで、4バイトには1900年1月1日からの経過日数（または過去にさかのぼった日数）が格納され、もう4バイトには午前0時からのクロックティック数（1ティック＝3.33ミリ秒）が格納されています。また、datetimeデータ型より精度は低くなりますが、4バイトのみを使用するsmalldatetimeデータ型もあります。smalldatetimeデータ型では、1900年1月1日から起算した日数と、午前0時から何分が経過したかが格納されます。数値はすべて整数として格納されます。従って、SELECT GETDATE() + 1では、実際は整数値にdatetimeデータを加算していることになります。datetime型の方が整数型よりも優先度が高いため、優先度の低い加数である整数1は、暗黙的にdatetimeデータ型に変換されます。
次の例では、1をdatetime型に変換しています。この結果は、1900年1月1日から1日が経過した日付として扱われます。
SELECT CAST(1 as datetime); Results: 1900-01-02 00:00:00.000
SELECT GETDATE() + 1では、内部的に整数として解釈される2つのdatetime値が加算されます。その結果、プラス（+）記号を使う加算演算が完全に有効になります。例えば、次のコードはすべて正しいステートメントです。
SELECT GETDATE(); SELECT DATEADD(dd, 1, GETDATE()); SELECT GETDATE() + 1; SELECT GETDATE() + 'Jan 02, 1900'; Results: 2007-03-16 23:01:37.420 2007-03-17 23:01:37.420 2007-03-17 23:01:37.420 2007-03-17 23:01:37.420
日付/時刻の計算で加算演算子（+）を使用するには、datetime型の加数が少なくとも1つ必要で、その加数は優先度が最も高くないといけません。
次の例を試してみてください。
SELECT DATEADD(dd, 1, 'Mar 17, 2007'); SELECT 'Mar 17, 2007' + 1; SELECT 'Mar 17, 2007' + 'Jan 02, 1900'; SELECT GETDATE() + 1 + 'Jan 02, 1900';
お分かりのように、最初のSELECTではSQL ServerのDATEADD()関数を使用しており、varchar型として記述されている日付を認識し、正しい結果を導き出します。2番目のSELECTは失敗し、「Conversion failed when converting the varchar value 'Mar 17, 2007' to data type int.（varchar型の値'Mar 17, 2007'をintデータ型に変換しているときに変換エラーが発生しました）」というエラーメッセージが表示されます。このエラーが発生するのは、varchar型は整数型よりも優先度が低いため、varchar型の加数を暗黙的に整数データ型に変換しなければならないからです。このような変換は不可能です。
3番目のSELECTは動作しますが、特に意味をなしません。これは、2つのvarchar式を連結しているだけにすぎません。4番目のSELECTはとても興味深いものです。2番目と3番目の加数は、1番目の加数よりも優先度が低いため、暗黙的にdatetimeデータ型に変換する必要があります。それぞれは1日として解釈されるため、GETDATE()関数の結果に2日が加算されます。
最初の落とし穴
この時点で、日付/時刻の計算に加減演算子を使ってもまったく問題ないと考え始めるかもしれません。ところが、そうでもないのです。例えば、2つの日付の間隔（日単位）を、次の2つの方法を使って計算してみましょう。
DECLARE @dt1 datetime, @dt2 datetime; SELECT @dt1 = 'Mar 17, 2007 09:09:00', @dt2 = 'Mar 17, 2007 22:09:00'; SELECT DATEDIFF(dd, @dt1, @dt2); SELECT CAST((@dt2 - @dt1) as int); Results: 0 1
SQL ServerのDATEDIFF()関数を使って計算するのと、減算演算子を使って計算するのでは結果が異なります。最初の結果（0）が正しく、2番目の結果（1）は間違っています。なぜこのような結果になるのでしょうか？ datetime値を整数に変換すると、その結果は一番近い整数に四捨五入されます。どのように四捨五入されるかは、datetime値の時間部分によって変わってきます。次の例を考えてみましょう。
DECLARE @dt3 datetime, @dt4 datetime; SELECT @dt3 = 'Mar 17, 2007 11:59:59.994', @dt4 = 'Mar 17, 2007 11:59:59.997'; SELECT CAST(@dt3 AS int); SELECT CAST(@dt4 AS int) SELECT CAST(CAST(@dt3 AS int) AS datetime); SELECT CAST(CAST(@dt4 AS int) AS datetime); Results: 39156 39157 2007-03-17 00:00:00.000 2007-03-18 00:00:00.000
この例では、正午少し前のタイムスタンプを2つ使用しています。この2つのタイムスタンプの間隔は3ミリ秒です。しかし2つの日付の値を整数に変換し、その結果を日付の値に再変換すると、1日のずれが生じます。同様に、異なる日付（3月16日と3月17日）を表す2つのdatetime値が、次のように同じ日付に誤って変換されることもあります。
DECLARE @dt3 datetime, @dt4 datetime; SELECT @dt3 = 'Mar 16, 2007 12:00:01.000', @dt4 = 'Mar 17, 2007 11:59:59.994'; SELECT CAST(@dt3 AS int); SELECT CAST(@dt4 AS int) SELECT CAST(CAST(@dt3 AS int) AS datetime); SELECT CAST(CAST(@dt4 AS int) AS datetime); Results: 39156 39156 2007-03-17 00:00:00.000 2007-03-17 00:00:00.000
ここで、もう1つの例を見てみましょう。例えば、ここにID、時間、価格といった販売取引内容が格納されているテーブルがあります。そして、このテーブルから、1日あたりの取引合計金額と取引数を調べる必要があるとします（よくある処理です）。非常に大きなテーブルなので、日付ではなく期間ごとに取引をグループ化し、クエリのスピードを上げることにしました。ただし、SQL ServerのDATEDIFF()関数は使用しません。代わりに、より「高度な」方法を使って、つまり、datetimeデータを整数に変換し、シンプルな算術減算を使って期間を計算します。
このシナリオではどうなるのかを見てみましょう。
SET NOCOUNT ON; IF OBJECT_ID('sales', 'U') IS NOT NULL DROP TABLE sales CREATE TABLE sales( transactionID int, transactionTime datetime, amount decimal(4,2)); INSERT INTO sales VALUES(1, 'Mar 17, 2007 08:00:23', 24.34); INSERT INTO sales VALUES(2, 'Mar 17, 2007 10:33:23', 88.54); INSERT INTO sales VALUES(3, 'Mar 17, 2007 12:00:44', 12.12); INSERT INTO sales VALUES(4, 'Mar 17, 2007 14:23:23', 43.25); INSERT INTO sales VALUES(5, 'Mar 17, 2007 16:45:22', 76.34); INSERT INTO sales VALUES(6, 'Mar 17, 2007 17:11:22', 51.11); INSERT INTO sales VALUES(7, 'Mar 17, 2007 19:45:23', 30.99); SELECT COUNT(*) AS #Trans, SUM(amount) AS totalAmount FROM sales GROUP BY DATEDIFF(dd, 0, transactionTime); SELECT COUNT(*) AS #Trans, SUM(amount) AS totalAmount FROM sales GROUP BY CAST(transactionTime AS int); SELECT COUNT(*) AS #Trans, SUM(amount) AS totalAmount FROM sales GROUP BY CAST((transactionTime - 0) AS int); Results: #Trans totalAmount ----------- --------------------------------------- 7 326.69 #Trans totalAmount ----------- --------------------------------------- 2 112.88 5 213.81 #Trans totalAmount ----------- --------------------------------------- 2 112.88 5 213.81
SQL ServerのDATEDIFF()は、FLOOR()関数と同じように日付を処理するため、正しい結果を導きだします。具体的には、DATEDIFF()は各日付の時間部分を削除し、日付部分のみを操作します。他の方法、つまり、整数に変換したり、直接減算したりする方法では、それぞれのdatetime値が時間部分の値を考慮して一番近い整数に四捨五入されます。そのため、2番目と3番目のSELECTステートメントは誤った結果になります。
