2014年2月17日

SQLテクニック（１）　日付型をWHERE句で比較する際の注意点（Oracle）

以下のような消費税率マスタテーブルがあるとします。

TAXRATE_TBL

—————————————————————————————–
TAXRATE　　STARTDATE  ENDDATE
(NUMBER)  (DATE)　　　(DATE)
—————————————————————————————–
0.03             1989/04/01       1997/03/31
0.05             1997/04/01       2014/03/31
0.08             2014/04/01       2100/12/31
※データベースはOracle
※STARTDATEとENDDATEには、時分秒は入れない

上記のテーブルから現在日付に沿ったTAXRATEを取得する場合に、以下のようなSQLを書きました。

【SQL例】

SELECT
　TAXRATE
FROM
　TAXRATE_TBL
WHERE
　SYSDATE ≧ STARTDATE
AND
　SYSDATE ≦ ENDDATE

しかし、このWHERE句では問題があります。

■SYSDATE(時刻あり)と時刻なしカラムで比較をしている　

SYSDATE ≧ STARTDATE
AND
SYSDATE ≦ ENDDATE

これでは、2014/03/31 00:00:00 ～ 2014/03/31 23:59:59の間は値を取れません。

　１．TAXRATE_TBL.STARTDATEは日付のみしか持っていません。
　２．しかし、SYSDATEは日付＋時間まで取得します。

現在時刻(SYSDATE)が2014/03/31 00:00:01とします。

　2014/03/31 00:00:01 ≧ 1997/04/01 00:00:00 ← ○
AND
　2014/03/31 00:00:01 ≦ 2014/03/31 00:00:00 ← ×

これでは、2014/03/31 00:00:01　～　2014/03/31 23:59:59の間はどこにもヒットしません。

正しくは、
WHERE
　TRUNC(SYSDATE) ≧ STARTDATE
AND
　TRUNC(SYSDATE) ≦ ENDDATE

または

WHERE
 TO_DATE(TO_CHAR(SYSDATE),'YYYY/MM/DD') ≧ STARTDATE
AND
 TO_DATE(TO_CHAR(SYSDATE),'YYYY/MM/DD') ≦ ENDDATE

の様に、SYSDATE側の時刻をカットします。