文字列を日付型に変換、和暦を西暦に変換

syntax

  • TO_DATE ( string [, format [, nls_param]] )
  • TO_DATE ( string [ DEFAULT def_val ON CONVERSION ERROR ] ↵
               [, format [, nls_param]] ) Oracle 12cr2

return [ datetime ]

parameter

string文字列式、数値式(ユリウス日)
def_val変換エラー時のデフォルト値(変換エラーの発生しない文字列式)
format日付書式のフォーマットdefault NLS_DATE_FORMAT
nls_param各国語サポートパラメータNLS セッションパラメータ依存

return

  • フォーマット書式に従って変換された日付
    Oracle 12c R2 以降の場合、format 書式にて変換出来ない値を def_val で戻すことが可能。

avail

  • SQL および PL/SQL の両方で使用可能。

変換エラー時のデフォルト値

フォーマットエラーとならないバインド変数または文字リテラル
カラム名は使用できない(Oracle 12c R2 時点)

日付書式のフォーマット文字列

(注意) DEFAULT ~ ON CONVERSION ERROR を使用した場合、リテラル に制限される。
  変数やカラムを使用したい場合 VALIDATE_CONVERSION と CASE 式で記述する。

各国語サポートパラメータ

NLS_CALENDAR についてはグローバリゼーション・サポート・ガイド参照

nls_param の種類設定例
NLS_DATE_LANGUAGEENGLISH
NLS_CALENDAR'JAPANESE IMPERIAL'(要クォート)

TO_DATE 関数の内容

文字列式 string をフォーマット文字列 format で日付型に変換する。
フォーマット文字列 format が省略されると NLS_DATE_FORMAT 書式が使用される。NLS_DATE_FORMAT 環境変数 または、セッションパラメータ。

TO_DATE 使用例

SQL> select str, fmt, TO_DATE(str, fmt) from to_date_sample1;
 
STR                            FMT                            TO_DATE(STR,FMT)
------------------------------ ------------------------------ -------------------
05-10-01                       RR-MM-DD                       2005-10-01 00:00:00
05-10月-01                     RR-MON-DD                      2005-10-01 00:00:00
991231235859                   RRMMDDHH24MISS                 1999-12-31 23:58:59
991231235859                   YYMMDDHH24MISS                 2099-12-31 23:58:59
2004-05-06 07:08:09            RRRR-MM-DD HH24:MI:SS          2004-05-06 07:08:09
2005-06-30 00:05:20            YYYY-MM-DD HH24:MI:SS          2005-06-30 00:05:20

Oracle 10g において MON 日付書式による月の表記が変更されている。
NLS_DATE_LANGUAGE が「日本」環境の場合に MON 書式で有効な日付要素は「n月」となる。Oracle 9i 以前では JAN などの英名表記で指定する。

Oracle 9i 以前の場合

SQL> select TO_DATE('05-OCT-01', 'YY-MON-DD')
  2   from dual;
 
TO_DATE('05-OCT-01'
-------------------
2005-10-01 00:00:00

Oracle 10g で同じ表記を使用したい場合には NLS_DATE_LANGUAGE で使用言語を明記する。

SQL> select TO_DATE('05-OCT-01', 'YY-MON-DD', 'NLS_DATE_LANGUAGE=ENGLISH'),
                        ^^^                                      ^^^^^^^
  2         TO_DATE('05-10月-01', 'YY-MON-DD', 'NLS_DATE_LANGUAGE=JAPANESE')
                        ^^^^                                      ^^^^^^^^
  3   from dual;
 
TO_DATE('05-OCT-01'   TO_DATE('05-10月-01
-------------------   -------------------
2005-10-01 00:00:00   2005-10-01 00:00:00

ユリウス日 (Julian Day Number)

SQL> alter session set NLS_DATE_FORMAT='SYYYY/MM/DD HH24:MI:SS';
セッションが変更されました。
 
SQL> select TO_DATE(1, 'J') from dual;
 
TO_DATE(1,'J')
--------------------
-4712/01/01 00:00:00  -- 起算時間(PM:0時)は切り捨てられている

和暦表記の文字列を西暦の日付型に変換する例

TO_DATE の各国語サポートパラメータ nls_param を使用することで年号を使った和暦表記文字列を日付に変換することもできる。

SQL> select
  2     TO_DATE('H19-01-01', 'EYY-MM-DD', 'NLS_CALENDAR=''JAPANESE IMPERIAL''') e_date
  3  from dual;
 
E_DATE
-------------------
2007-01-01 00:00:00
SQL> select
  2     TO_DATE('平成19-01-01', 'EEYY-MM-DD', 'NLS_CALENDAR=''JAPANESE IMPERIAL''') ee_date
  3  from dual;
 
EE_DATE
-------------------
2007-01-01 00:00:00

書式 EYY/EEYYY ⇒ 書式モデル(日付)

取り扱い注意:TO_DATE の nls_param 指定について NLS_CALENDAR の表記が グローバリゼーション・サポート・ガイド と SQL マニュアルでは E/EE 書式は TO_datetime ファンクションでの使用が 「いいえ」 で相反した内容になっている。
利用できる機能をまだ明記しないことはあっても使用することを否定している事はめずらしい。現状では使ってよいかわからない状態の機能。マニュアルの誤記であることが確認できるか 「サポートから使用のお墨付きをもらってから」プログラム中で使用するほうが良いだろう。(Oracle 10g R2 現在)

セッションパラメータ(初期化パラメータ) NLS_CALENDAR='JAPANESE IMPERIAL' を設定する方法もある。
確認が取れない場合に和暦表記から西暦への変換方法 ⇒ セッションパラメータで和暦の日付を入力させる方法

(補足)和暦表示に変換する (詳細は TO_CHAR(日付) へ)

SQL> select to_char(date '1900-01-01', 'EEDL', 'NLS_CALENDAR=''JAPANESE IMPERIAL''')
  2  from dual;
 
TO_CHAR(DATE'1900-01-01','EEDL','NLS_CALEN
------------------------------------------
明治33年1月1日 月曜日

DEFAULT ... ON CONVERSION ERROR 句の使用例

変換時にエラーが発生する可能性ある文字列の例 Oracle 12cr2

SQL> alter session set NLS_DATE_FORMAT='yyyy/mm/dd hh24:mi:ss';
 
セッションが変更されました。
 
SQL> select
  2     TO_DATE('2000-01-02' DEFAULT null ON CONVERSION ERROR, 'yyyy-mm-dd') VALID_DATE,
  3     TO_DATE('2000/01/02' DEFAULT null ON CONVERSION ERROR, 'fxyyyy-mm-dd') INVALID_DATE1,
  4     TO_DATE('2000-01-32' DEFAULT null ON CONVERSION ERROR, 'yyyy-mm-dd') INVALID_DATE2
  5  from dual;
 
VALID_DATE	    INVALID_DATE1       INVALID_DATE2
------------------- ------------------- -------------------
2000/01/02 00:00:00 <NULL>              <NULL>

DEFAULT 句 使用時の デフォルトに指定する値 はフォーマット文字列規則に則った文字列式(カラム名は使用できない)

SQL> variable def_val varchar2(30)='2000-02-03' /* 定義時代入できるのは Oracle 12cR2 以降*/
SQL> select
  2    TO_DATE('2000-01-32' DEFAULT :def_val ON CONVERSION ERROR, 'yyyy-mm-dd')
  3  from dual;
 
TO_DATE('2000-01-32
-------------------
2000/02/03 00:00:00
SQL> select
  2    TO_DATE('2000-01-32' DEFAULT DATE '2000-01-01' ON CONVERSION ERROR, 'yyyy-mm-dd')
  3  from dual;
  TO_DATE('2000-01-32' DEFAULT DATE '2000-01-01' ON CONVERSION ERROR, 'yyyy-mm-dd')
                               *
行2でエラーが発生しました。:
ORA-01830: 日付書式の変換で不要なデータが含まれています
SQL> select
  2    TO_DATE('2000-01-02' DEFAULT '2000-01-32' ON CONVERSION ERROR, 'yyyy-mm-dd')
  3  from dual;
  TO_DATE('2000-01-02' DEFAULT '2000-01-32' ON CONVERSION ERROR, 'yyyy-mm-dd')
                               *
行2でエラーが発生しました。:
ORA-01847: 月単位の日付は1から月末日の間で指定する必要があります

DEFAULT 句 使用時の フォーマット文字列 には文字リテラルを使用する必要あり (バインド変数とカラム名は使用できない)

SQL> variable fmt varchar2(30)='yyyy-mm-dd'
 
SQL> select
  2    TO_DATE('2000-01-32' DEFAULT null ON CONVERSION ERROR, :fmt)
  3  from dual;
  TO_DATE('2000-01-32' DEFAULT null ON CONVERSION ERROR, :fmt)
                                                          *
行2でエラーが発生しました。:
ORA-43918: この引数はリテラルである必要があります

フォーマット文字列に文字列変数を使用したい場合には判定と変換を分割する。

SQL> select
  2    case when validate_conversion('2000-01-32' AS DATE, :fmt) = 1 then
  3      TO_DATE('2000-01-32', :fmt) else null 
  4    end INVALID_DATE
  5  from dual;
 
INVALID_DATE
-------------------
<NULL>

日付フィールドの補完に関する豆知識

日付フィールドの自動補完

TO_DATE は YYYY/MM/DD HH24:MI:SS のフィールドを全て指定する必要がない。 自動的に日付として適切になるように空白部が補完される。

現在日付が 2001年12月31日 23時59分59秒における日付要素の補完例

TO_DATE('2000', 'YYYY') ⇒ 2000/12/01 00:00:00
TO_DATE('12 23', 'MM HH24') ⇒ 2001/12/01 23:00:00
TO_DATE('30', 'SS') ⇒ 2001/12/01 00:00:30
補正値表
フィールドデフォルト値
現在の年
現在の月
1 日
0 時
0 分
0 秒

NLS_DATE_FORMAT、NLS_CALENDAR の現在値

SQL> select * from V$NLS_PARAMETERS
  2   where parameter in ('NLS_DATE_FORMAT', 'NLS_CALENDAR');
 
PARAMETER                  VALUE
-------------------------- ------------------------------
NLS_CALENDAR               GREGORIAN
NLS_DATE_FORMAT            YYYY-MM-DD HH24:MI:SS
 
SQL> alter session set nls_calendar='JAPANESE IMPERIAL';
SQL> select * from v$nls_parameters
  2   where parameter in ('NLS_DATE_FORMAT', 'NLS_CALENDAR');
 
PARAMETER                  VALUE
-------------------------- ------------------------------
NLS_CALENDAR               Japanese Imperial
NLS_DATE_FORMAT            EEYY"年"MM"月"DD"日"
 


SQL 関数 - TO_DATE 関連

関連事項

 
アルファベット別 関数一覧 ショートカット
ABCDEFG
HILMNOP
RSTUVWX
日本オラクル
■ 日本オラクル 株式会社
■ オラクルマスター資格 (オラクルマスターとは
■ オラクルサポートセンター