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
１つ目の例では文字列に時刻がなく、日付しか指定されていないときに0時0分0秒と解釈されている。
これはまだわかるんだけど、２つ目の例では'-'や':'といった区切り文字がない文字列がきちんとパースされている。
そこで、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
２つ目、３つ目の例ではきちんとエラーが発生するようになった。
これはどこまでのデータチェックが必要かという状況にもよるけど、Oracleの仕様として覚えておいたほうがよさそう。
0 件のコメント:
コメントを投稿