日付の指定

SQLで、where句に日付を指定する場合についてのちょっとしたメモ。

データ作成日([create_datetime])が2016年8月のものを抽出したいとする。なお、このカラムはdatetime型であることとする。

where
[create_datetime] >= '2016-08-01' and
[create_datetime] <= '2016-08-31'


このように条件を指定すれば、2016年8月に作成されたデータがすべて抽出できるかというと、そういうわけではない。
2行目FROM「'2016-08-01'以上」の方は問題ないのだけれども、3行目TO「'2016-08-31'以下」は、実際には「'2016-08-31 00:00:00 000'以下」だから、この時刻を1msでも越えたら範囲外になる。だから、この条件では、事実上、「'2016-08-31'」のデータは抽出されない。

3行目TOを変更して、

where
[create_datetime] >= '2016-08-01' and
[create_datetime] < '2016-09-01'


とすれば、「'2016-09-01'未満」となるから8月分のデータはすべて抽出できる。だからこれで良しと言えないことはないのだけれども、何かのツールに組み込んでこれをパラメーターにする場合、ユーザーに常にプラス1日で入力して貰うことには、理解を得がたい場合もあるだろう。

そこで、次のようなSQL文を書く人もいる。

where
[create_datetime] >= '2016-08-01' and
convert(date,[create_datetime]) <= '2016-08-31'


[create_datetime]の値をdate型に変換すれば、8月31日のデータはすべて'2016-08-31'という形になるから、この条件で8月分のデータはすべて抽出できることになる。

僕も、最初の内はこういう書き方をしていたのだけれども、これだと[create_datetime]をすべてdate型に変換した上で、その変換結果の中から該当日付のデータを抽出することになるので、処理としては重くなる。[create_datetime]にインデックスが張られれていたとしても、convert(date,[create_datetime])に対してそのインデックスが効くわけではない。

それで、こんな書き方をするようにした。

where
[create_datetime] >= '2016-08-01' and
[create_datetime] < dateadd(day,1,'2016-08-31')


「'2016-08-31'プラス1日(='2016-09-01')未満」という条件になるから、これで8月分のデータがすべて抽出できる。前のSQLと違って、検索するカラムのデータに対して型変換を掛けているわけではないから、処理が無用に重くなるということもないし、毎回1日足す必要がないからユーザーフレンドリーでもある。
たまに、手を抜いて(?)こんな書き方をしている人を見掛けることがある。

where
convert(char(6),[create_datetime], 112) = '201608'


確かに賢いと言えば賢い。けれどもこれも先ほど書いたのと同じ理由であまりお勧めはできない。
軽いDBで特にほかに影響が出るようなことがないなら、これでも良いかもしれない。

コメントの投稿











管理者にだけ表示を許可する

トラックバック

この記事のトラックバックURL
http://hoshinahouse.blog101.fc2.com/tb.php/1122-a68840b3