今回から「日データ関連関数紹介」として、DATEに関する情報を、３回にわたってお送りします。今回はその１回目として、「データタイプ間の日付データの制御」に関する話題をお使えします。では、早速始めましょう。

---

８.１　データタイプ間の日付データの制御

８.１.１　DATE照会-エラーが発生する場合

　一般的に日データを抽出しようとする時発生するエラーの大部分はDATEタイプに対する誤った演算により発生します。 以下で紹介する三つ演算はSQL作成者がよく失敗するDATE演算です。

　実際にエラーが発生するのか例題を直接実行してみることにしましょう。

• DATE      +  DATE
• CHAR      –  DATE
• NUMBER  –  DATE

SQL> SELECT TO_DATE('20110101','yyyymmdd') + TO_DATE('20110201','yyyymmdd')
       FROM DUAL ;

ERROR at line 1:
ORA-00975: date + date not allowed

SQL> SELECT '20110101' - TO_DATE('20110101','yyyymmdd') FROM DUAL ;

ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CHAR got DATE

SQL> SELECT 20110101 - TO_DATE('20110101','yyyymmdd') FROM DUAL ;

ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got DATE

８.１.２　DATE照会-正常照会される場合

　前項でエラーが発生するDATE演算に対して調べてみました。。 今回は例題を通じて正常な演算になる場合に対して調べてみることにしましょう。 以下の例題を実行してみて結果値がどのように出てくるのか確認してみます。

SELECT TO_CHAR(TO_DATE('20110101','yyyymmdd') - TO_DATE('20110101','yyyymmdd')) 
AS "Date" FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('20110101','yyyymmdd') + 1,'yyyymmdd') FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('20110101','yyyymmdd') - 1,'yyyymmdd') FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('20110101','yyyymmdd') + '1','yyyymmdd') FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('20110101','yyyymmdd') - '1','yyyymmdd') FROM DUAL
UNION ALL
SELECT TO_CHAR(1 + TO_DATE('20110101','yyyymmdd'),'yyyymmdd') FROM DUAL
UNION ALL
SELECT TO_CHAR('1' + TO_DATE('20110101','yyyymmdd'),'yyyymmdd') FROM DUAL ;

Date
---------
0          --->  DATE   - DATE
20110102   --->  DATE   + NUMBER
20101231   --->  DATE   - NUMBER
20110102   --->  DATE   + CHAR
20101231   --->  DATE   - CHAR
20110102   --->  NUMBER + DATE
20110102   --->  CHAR   + DATE

８.１.３　誤った日演算

８.１.３.１　CHAR – NUMBERで日を計算する場

　日データを持つカラムのデータ タイプがCHARまたはVARCHAR2の場合、以下のような失敗を犯すことがあります。 カラムのデータ タイプを確認して日演算を実行しなければならないのですが、単純にカラム名だけでデータ タイプを誤解して誤った演算をすることになる場合です。

　以下のSQL[1]はDATE – 2ですので日データが正常な抽出になりますが、SQL[2]の場合はCHAR-2で日計算でない数字計算になって誤ったデータを抽出することになります。

　特にSQL[2]とともに作成された演算をプログラムに適用すれば、エラーが発生しないために発見することも難しく、間違った結果値が抽出されてデータの整合性まで毀損されることがあるので注意しなければなりません。

SQL[1]正常日データ計算

SELECT TO_DATE('20111001','yyyymmdd') - 2 AS "Date" FROM DUAL ;

Date
--------
11/09/29  --->  Date - Number => DATE Datatype

 	SQL[2]正常ではない日データ計算
SELECT '20111001' - 2 AS "Date" FROM DUAL ;

Date
----------
20110999  --->  Char - Number => Number Datatype

８.１.３.２　日カラム データをNVL()処理時

CASE WHEN NVL(A.EMPTY_DATE, SYSDATE) > TO_DATE(A.FREE_DATE, 'YYYYMMDD')
THEN 1 ELSE 0 END

　上の例文もまた、日データ演算時たくさんする失敗する例のうちの一つです。

　もし、上の例文でA.EMPTY_DATEカラムのデータ タイプがDATEタイプでないCHARあるいはVARCHAR2タイプの場合NVL関数の特性上エラーが発生します。 これはNVL(EXPR1,EXPR2)でリターンされる値のデータ タイプは常にEXPR1のデータ タイプであるためです。 再び上の例文に戻って説明すれば、A.EMPTY_DATEでNULLデータが抽出される時にはCHARタイプとSYSDATEが比較されるのでエラーが発生するのです。 作成意図はTO_DATE > TO_DATEの比較でしたが、カラム データ タイプがCHARの場合にはNVL関数適用後リターンされる値がCHARであるためエラーが発生したのです。 基本的に上のような比較はデータ タイプに対する調査を必ず先行して行うことでエラーを避けることができます。

　それではNVL(EXPR1,EXPR2)関数でEXPR1カラムがCHARあるいはVARCHAR2タイプの場合、どのように変更すれば良いでしょうか？ 次のようにSQLを変更すれば正常実行できます。

CASE WHEN  TO_DATE(NVL(A.EMPTY_DATE, TO_CHAR(SYSDATE,'YYYYMMDD')),'YYYYMMDD') >
TO_DATE(A.FREE_DATE, 'YYYYMMDD') THEN 1 ELSE 0 END

---

　今回は、ここまでです。以下でしたでしょうか？次回は、「日付、時間、分、秒の計算」についてです。それでは、See You ^^

---