OracleでDate型の時刻が00:00:00になる原因

OracleでDate型の時刻が00:00:00になる原因

OracleのDATE型には年月日のみならず、時分秒まで格納されている。以前アプリケーションを開発した際に、このDATE型の「時分秒」の値を利用した。ところが、その時刻が00:00:00となってしまって悩まされた。実は、Date型の出力フォーマットを設定出来ることが原因だった。このときの経験と問題への対処法をまとめておく。

関連記事：

目次

時分秒が00:00:00になってしまった!

OracleのDATE型は取得時に特に指定しない場合、NLS_DATE_FORMATというOracleの環境変数で定義された出力フォーマットが適用される。NLS_DATE_FORMATは以下の方法で確認する事が出来る。

SQL> select * from nls_session_parameters
     where parameter = 'NLS_DATE_FORMAT';

PARAMETER        VALUE
--------------------------------------------------
NLS_DATE_FORMAT  RR-MM-DD

そのため、DATE型の値を特に意識せずに取得すると、環境によって取得できる値が変わってしまう。私が遭遇したのは、社内に作成した環境では時分秒まで取ることができるのに、実際に導入を行う環境では時分秒が00:00:00になってしまうという現象だった。上のようにNLS_DATE_FORMATがRR-MM-DDに設定されてている場合は、時分秒が00:00:00となってしまうのだ。

時分秒まで取得できるようにNLS_DATE_FORMATの設定を変更する場合、以下のようにする必要がある。

SQL> ALTER SESSION SET NLS_DATE_FORMAT='yyyy/mm/dd hh24:mi:ss';

セッションが変更されました。

SQL> select * from nls_session_parameters
     where parameter = 'NLS_DATE_FORMAT';

PARAMETER        VALUE
--------------------------------------------------
NLS_DATE_FORMAT  yyyy/mm/dd hh24:mi:ss

DATE型を取得する時はTO_CHAR関数を使うべし

毎回環境を設定することが出来れば良いが、そうではない場合も多いだろう。そんな時にはTO_CHAR関数を利用して出力フォーマットを指定すればよい。以下のように記述することで、NLS_DATE_FORMATに影響されずに出力フォーマットを直接指定する事が出来る。

SQL> select sysdate from dual;

SYSDATE
--------
08-04-28

SQL> select to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YYYY/MM/DDHH24:MI:SS'
--------------------------------------
2008/04/28 16:19:44

ほんの一手間だが、DATE型の値を取得する際にTO_CHAR関数を利用することで環境に左右されないSQL文を書くことが出来る。こういった一手間を惜しまないことで堅牢なプログラムが生み出されていくのではないだろうか。

参考サイト：

Oracle Database 概要 – 26章 SHIFT the Oracle

関連記事：

Page Top