[Oracle] TO_DATEでのFX書式文字列

TO_DATEの書式指定

Oracleでテーブルに日付を格納するときにはTO_DATEで文字列からDATEやTTIMESTAMPに型変換したりする。
このとき、'YYYYMMDD'とかで書式指定すれば、文字列を書式に合わせてパースしてくれる。

> SELECT TO_CHAR(TO_DATE('2017-02-28 12:34:56', 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
2017-02-28 12:34:56

ところが、あるデータで書式指定に合ってないのになんの問題もなく、テーブルにデータが格納してしまっていることに気づいた。

> SELECT TO_CHAR(TO_DATE('2017-02-28', 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
2017-02-28 00:00:00

> SELECT TO_CHAR(TO_DATE('20170228 123456', 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
2017-02-28 12:34:56

1つ目の例では文字列に時刻がなく、日付しか指定されていないときに0時0分0秒と解釈されている。
これはまだわかるんだけど、2つ目の例では'-'や':'といった区切り文字がない文字列がきちんとパースされている。

そこで、Oracleの日付書式について、調べてみると

Oracle® Database SQL言語リファレンス 11gリリース2(11.2) 日時書式モデル
文字列から日付への変換に関する規則

次の追加の書式化規則は、文字列値を日付値に変換する場合に適用されます(ただし、書式モデルで修飾子FXまたはFXFMを使用して書式検査を制御した場合は適用できません)。

・先行0(ゼロ)を含む数値書式要素の桁がすべて指定されている場合は、日付文字列から書式文字列に含まれる句読点を省略できます。たとえば、MM、DD、YYなどの2桁の書式要素については、2のかわりに02を指定した場合です。

・日付文字列から、書式文字列の最後にある時刻フィールドを省略できます。

・日付文字列では、英数字以外の任意の1文字を使用して、書式文字列の句読点記号に一致させることができます。

どうもOracleの仕様として、句読点の省略や、時刻フィールドの省略は文字列から日付に変換するときの規則のようだ。

日付書式の'FX'

これはこれで便利な機能ではあるけれど、厳密に書式に合致させたい場合はどうすればいいんだろうか。

Oracle® Database SQL言語リファレンス 11gリリース2(11.2) 表3-15 日時書式要素

FX 文字データと書式モデルが完全に一致する必要があります。

さっきの変換規則のところで、すでに出てきていた感はあるけど、'FX'というのを使えばいいみたい。

では、書式文字列の先頭に'FX'を付加してみよう。

> SELECT TO_CHAR(TO_DATE('2017-02-28 12:34:56', 'FXYYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
2017-02-28 12:34:56

> SELECT TO_CHAR(TO_DATE('2017-02-28', 'FXYYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
ORA-01840: input value not long enough for date format

> SELECT TO_CHAR(TO_DATE('20170228 123456', 'FXYYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
ORA-01861: literal does not match format string

2つ目、3つ目の例ではきちんとエラーが発生するようになった。

これはどこまでのデータチェックが必要かという状況にもよるけど、Oracleの仕様として覚えておいたほうがよさそう。

0 件のコメント:

コメントを投稿