SQL：日付範囲の比較で四苦八苦した - ソフトウェア開発者の日常

考え始めたときは、CASE文を使うとは思っていなかった。

f:id:AJYA:20120828214158j:plain

photo credit: therefromhere via photo pin cc

ことの発端

期間の判定で、6/1～7/15の間に今日の日付が入っているか判定するのは、SQLのWHEREで

6/1 <= 今日の年月 AND 今日の年月 <= 7/15

と書けばいい。

8/1～1/31(翌年を指している)の場合は、年があれば上記と同じでいいが、年がない場合は、単純には比較できない。

検討1

今年の2012を取得して、加算して翌年の2013を求めて、

2012/8/1 <= 今日の年月 AND 今日の年月 <= 2013/1/31

とすればできると考えた。

2013年になるとどうなるか考えたら、1/10の場合、

2013/8/1 <= 2013/1/10 AND 2013/1/10 <= 2014/1/31

となってしまい、条件が正しくないことがわかった。

検討2

検討1での駄目な点を改善するには、2012/8/27だったら、

(2012/1/1 <= 2012/8/27 AND 2012/8/27 <= 2012/1/31) OR (2012/8/1 <= 2012/8/27 AND 2012/8/27 <= 2012/12/31)

とすれば判定できる。

2013/1/10でも、同様に

(2013/1/1 <= 2013/1/10 AND 2013/1/10 <= 2013/1/31) OR (2013/8/1 <= 2013/1/10 AND 2013/1/10 <= 2013/12/31)

で正しく判定できることに気がついた。

実装

1回ではできないので、まず検討2の範囲の比較ができるように、開始と終了日を生成するビューを作った。

CASE文で、6/1～7/15のパターンか、8/1～1/31のパターンのどちらか判定して、日付型のカラムを加工したら、SQL Serverでの記述はこんな感じになった。

start1 = CASE WHEN SUBSTRING(CONVERT(char, start, 111), 6, 5) < SUBSTRING(CONVERT(char, end, 111), 6, 5) THEN SUBSTRING(CONVERT(char, GETDATE(), 111), 1, 4) + '/' + SUBSTRING(CONVERT(char, start, 111), 6, 5)

ELSE SUBSTRING(CONVERT(char, GETDATE(), 111), 1, 4) + '/01/01'

END,

end1 = CASE WHEN SUBSTRING(CONVERT(char, start, 111), 6, 5) < SUBSTRING(CONVERT(char, end, 111), 6, 5) THEN SUBSTRING(CONVERT(char, GETDATE(), 111), 1, 4) + '/' + SUBSTRING(CONVERT(char, end, 111), 6, 5)

ELSE SUBSTRING(CONVERT(char, GETDATE(), 111), 1, 4) + '/' + SUBSTRING(CONVERT(char, end, 111), 6, 5)

END,

start2 = CASE WHEN SUBSTRING(CONVERT(char, start, 111), 6, 5) < SUBSTRING(CONVERT(char, end, 111), 6, 5) THEN SUBSTRING(CONVERT(char, GETDATE(), 111), 1, 4) + '/' + SUBSTRING(CONVERT(char, start, 111), 6, 5)

ELSE SUBSTRING(CONVERT(char, GETDATE(), 111), 1, 4) + '/' + SUBSTRING(CONVERT(char, start, 111), 6, 5)

END,

end2 = CASE WHEN SUBSTRING(CONVERT(char, start, 111), 6, 5) < SUBSTRING(CONVERT(char, end, 111), 6, 5) THEN SUBSTRING(CONVERT(char, GETDATE(), 111), 1, 4) + '/' + SUBSTRING(CONVERT(char, end, 111), 6, 5)

ELSE SUBSTRING(CONVERT(char, GETDATE(), 111), 1, 4) + '/12/31'

END

このビューを使うビューを作って、そこで期間に入っているか判定をするようにした。