はじめに
職場でOracleのアップグレード（11g→19c）をすることになったのでバージョン間での互換性の問題などを調べていたところ、以下の記事を見つけました。
上記の記事ではTO_DATE関数の挙動が変わってTO_DATE('20190520','YYMMDD')がエラーになる一方で、TO_DATE('20120520','YYMMDD')はエラーにならないということでしたが、これは日付を解析するパーサーが変わったためではないかとのことでした。
よく使われる日付フォーマットは、上記の記事で触れられているYYMMDD以外にも幾つかあるため、日付フォーマットを変えてTO_DATE関数の挙動を調べてみることにしました。
テストを実行した環境
- Oracle社が提供しているOracle Live SQLでOracle19cを利用しました。
テスト
エラーが発生したSQL
- 以下のSQLを実行すると、
ORA-01843: not a valid monthが発生しました。
-- 日付文字列がYYMMDD
SELECT TO_DATE('120520','YYYYMMDD') FROM dual;
SELECT TO_DATE('130520','YYYYMMDD') FROM dual;
-- 日付文字列がYYYYMMDD
SELECT TO_DATE('20130520','YYMMDD') FROM dual;
正常に実行できたSQL
- 以下のSQLを実行すると、
20-MAY-12（2012年5月20日）もしくは20-MAY-13（2013年5月20日）が返されました。
-- 日付文字列がYYMMDD
SELECT TO_DATE('120520','YYMMDD') FROM dual;
SELECT TO_DATE('130520','YYMMDD') FROM dual;
-- 日付文字列がYY/MM/DD
SELECT TO_DATE('12/05/20','YY/MM/DD') FROM dual;
SELECT TO_DATE('13/05/20','YY/MM/DD') FROM dual;
SELECT TO_DATE('12/05/20','YYYY/MM/DD') FROM dual;
SELECT TO_DATE('13/05/20','YYYY/MM/DD') FROM dual;
-- 日付文字列がYY.MM.DD
SELECT TO_DATE('12.05.20','YY.MM.DD') FROM dual;
SELECT TO_DATE('13.05.20','YY.MM.DD') FROM dual;
SELECT TO_DATE('12.05.20','YYYY.MM.DD') FROM dual;
SELECT TO_DATE('13.05.20','YYYY.MM.DD') FROM dual;
-- 日付文字列がYYYYMMDD
SELECT TO_DATE('20120520','YYMMDD') FROM dual;
SELECT TO_DATE('20120520','YYYYMMDD') FROM dual;
SELECT TO_DATE('20130520','YYYYMMDD') FROM dual;
-- 日付文字列がYYYY/MM/DD
SELECT TO_DATE('2012/05/20','YY/MM/DD') FROM dual;
SELECT TO_DATE('2013/05/20','YY/MM/DD') FROM dual;
SELECT TO_DATE('2012/05/20','YYYY/MM/DD') FROM dual;
SELECT TO_DATE('2013/05/20','YYYY/MM/DD') FROM dual;
-- 日付文字列がYYYY/MM/DD
SELECT TO_DATE('2012.05.20','YY.MM.DD') FROM dual;
SELECT TO_DATE('2013.05.20','YY.MM.DD') FROM dual;
SELECT TO_DATE('2012.05.20','YYYY.MM.DD') FROM dual;
SELECT TO_DATE('2013.05.20','YYYY.MM.DD') FROM dual;
まとめ
- Oracle19cの場合、特定の日付文字列と日付フォーマットの組み合わせでエラーが起きることが分かりました。
- Oracle曰く「これはバグではない、仕様である」とのことらしいので、TO_DATE関数を使っている箇所を一通り調べた方が良さそうです。
- 年月日の間にスラッシュやピリオドが入っている場合は、いずれもエラーが発生しませんでした。
- こちらの記事でも指摘されているように、区切り文字のおかげで日付文字列を正確に解析できているのだと思います。
- 区切り文字がなくても、日付文字列と一致する日付フォーマットの場合には、正しい日付を取得出来ていました。
- 当たり前のことですが、日付文字列と日付フォーマットは確実に一致させておくべきでしょう。