Oracle SQLチューニング（第32回）「日データ関連関数紹介」（1／3回）

2025-06-09 05:58Oracle技術情報

Oracle SQLチューニング

今回から「日データ関連関数紹介」として、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 ^^

Oracle SQLチューニング（第32回）「日データ関連関数紹介」（1／3回）

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

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

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

８.１.３　誤った日演算

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

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

前の記事

次の記事

他社に頼らず自社でデータベースを監視・運用をしませんか？
MaxGaugeがサポートします

人気記事ランキング

タグ一覧

Oracle SQLチューニング（第32回）「日データ関連関数紹介」（1／3回）

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

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

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

８.１.３ 誤った日演算

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

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

前の記事

次の記事

他社に頼らず自社でデータベースを監視・運用をしませんか？MaxGaugeがサポートします

人気記事ランキング

タグ一覧

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

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

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

８.１.３　誤った日演算

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

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

他社に頼らず自社でデータベースを監視・運用をしませんか？
MaxGaugeがサポートします