Oracle DATE型の表示フォーマットに関する注意点

2018.12.10 株式会社システムインテグレータ

SI Object Browser for Oracle (以下、OB) でDATE型 (日付型) のデータを扱う方は多いかと思います。
OBではDATE型の表示や編集を簡単に行えますが、若干の注意が必要な事項があります。今回の記事ではこの注意点について紹介していきます。

SQL*PlusとOBのDATE型表示フォーマットの違い

OBとSQL*Plusでは、DATE型データを表示する際に、それぞれ異なるフォーマット設定を参照するため、表示結果に差異が生じる場合があります。

OBでは、オプション設定「日付型の表示書式」で指定されたフォーマットを使用します。
初期設定は次の通りです。
日付: yyyy/MM/dd (例: 2018/12/10)
時刻: HH:mm:ss (例: 15:00:00)
SQL*Plusでは、環境変数nls_date_formatで指定されたフォーマットを使用します。
この環境変数の初期設定は日付のみでRR-MM-DD (例 18-12-10) です。

ためしに「DATE型」で設定されカラム１つのみが定義されたテーブルTEST_TABLEの情報をOBのSQL実行画面とSQL*Plusでそれぞれ表示してみます。
SQL文は同じものを用います。

SELECT DATE_COLUMN FROM DATE_TEST

結果

図1 SQL実行画面で日付データを表示した結果

図2 SQL*Plusで日付データをを表示した結果

注意すべきは、TO_CHAR関数でDATE型を文字列型に変換する場合です。
第2引数を指定しない場合、環境変数nls_date_formatで指定されたフォーマットで文字列に変換されます。そのため、OBではDATE型の表示結果とTO_CHAR関数を適用した後の表示結果では値が異なることがあります。

図3では以下のSQLを実行し、結果を表示しています。

SELECT DATE_COLUMN, TO_CHAR(DATE_COLUMN) FROM DATE_TEST

図3 TO_CHAR関数を適用した結果を表示

OBの日付フォーマット変更 & nls_date_formatへの同期

OBではオプション設定でDATE型の表示フォーマットを変更できます。
「オプション」画面の「データ」タブに「日付型の表示書式」設定があります (図4)。

図 4 「日付型の表示書式」設定

ここではOBのSQL実行画面における表示書式の変更が行えます。
フォーマットの設定値については、Oracleのマニュアルを参照してください。
※一部対応していないフォーマットがあります。

「日付型の表示書式」設定をOracle Databaseに反映 (同期) させる方法もあります。
このやり方は、NLS_FORMAT_DATE設定を「前項で指定した日付と時刻両方の書式をセット」または「前項で指定した日付書式のみをセット」に設定するのみです。
この設定を行うと、OBからデータベースへログインした際に毎回ALTER SESSION 文が内部的に発行され、データベースセッションに対して「日付型の表示書式」と同じフォーマットが設定されます。
※時刻表記でHHが指定された場合内部的にHH24へ変換されます。

まとめ

DATE型データの表示フォーマットの違いについて紹介しました。
OBと、OB以外のソフトウェアではDATE型のデータが異なる形式で表示される場合があると思われますが、多くの場合、設定の違いによるものです。
OBの設定やOracleセッションの環境変数設定をご確認ください。
また、OBでは表示する日付フォーマットが変更できるので、開発プロジェクトに合わせた設定をすることでツールの表示差異を無くすことが可能ですので、有効に本機能をご活用いただければと思います。