Top > SQL 関数一覧 (T‐Z) > 文字列が日付に変換できるかチェックする

日付の文字列が正しい日付かチェックする

Oracle 12c R2 以降の場合 ⇒ VALIDATE_CONVERSION という組み込み関数がある。

VALIDATE_CONV_DATE ファンクション (日付文字列の有効性チェック)

文字列型の日付の整合性をチェックする関数は組み込み関数およびパッケージに用意されていない。 不正な日付文字をチェックなしに TO_DATE 関数 を使用すると例外が発生する。 これは外部入力データの投入バッチ処理などにおいて、トランザクション がロールバックすることにつながる。

CSV や文字列、テキストベースで用意されたデータをデータベースに投入する際の日付のチェック処理は欠かせない。

  • VALIDATE_CONV_DATE ファンクションのソース

    移行しやすいように VALIDATE_CONVERSION の仕様に近づように名称と引数、引数が NULL 場合の戻り値などの仕様を変更。

日付文字列のチェック形式は引数で変更できるが、セッションの NLS 関連パラメータ依存ではなくハードコーディング

CREATE OR REPLACE FUNCTION RIVUS.VALIDATE_CONV_DATE(
  P_DATE         IN VARCHAR,
  P_FORMAT       IN VARCHAR := 'YYYY-MM-DD HH24:MI:SS' ,
  P_NLS_PARAMTER IN VARCHAR2 := q'{NLS_CALENDAR = 'GREGORIAN'}' 
)
RETURN NUMBER
AS
  vDate          DATE;
BEGIN
  vDate := TO_DATE(P_DATE, P_FORMAT, P_NLS_PARAMTER);
  RETURN 1;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 0;
END;
/

VALIDATE_CONV_DATE 使用例

SELECT
    DECODE(VALIDATE_CONV_DATE(DATE_STR), 1, TO_DATE(DATE_STR), TO_DATE('9999/12/31'))
FROM DATE_STRING ;

q'{NLS_CALENDAR = 'GREGORIAN'}' は代替引用符を使った記述方法

 


関連事項

日本オラクル
■ 日本オラクル 株式会社
■ オラクルマスター資格 (オラクルマスターとは
■ Oracle Web セミナー