Oracle® 非公式 SQL, PL/SQL & DBA's リファレンス

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

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

return [ datetime ]

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

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

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

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

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

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

• 日付式のフォーマットに使用する文字列

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

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

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

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 句の使用例

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

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('20', 'DD') ⇒ 2001/12/20 00:00:00
TO_DATE('30', 'SS') ⇒ 2001/12/01 00:00:30
-- alter session set nls_date_format='YYYY-MM-DD:DAY';
TO_DATE('7', 'D') ⇒ 2001-12-01:土曜日
TO_DATE('1', 'D') ⇒ ORA-01835: 曜日とユリウス日が一致していません(※1) 

(※1) 'D'(≠'DD') の場合 以下の表のルールに従って '2001年1月1日 0時0分0秒' が補完される。指定した '1' （日曜日）と実際の曜日 '7' （土曜日）で一致しないため曜日とユリウス日での曜日が不一致となる。

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 関連

• 日にちの足し算と引き算に関する豆知識
• 別のタイムゾーン日付をセッションタイムゾーン日付にする ⇒ expr at time zone sesssiontimezone
• タイムスタンプ型をタイムゾーン付きに変換するには ⇒ from_tz ( t, tz )
• 日付を標準時に変換する ⇒ sys_extract_utc ( t )
• 日付を UNIXTIME に変換する ⇒ unixtime ( t )
• n 年を期間型に変換する ⇒ numtoyminterval( n )
• m 日を期間型に変換する ⇒ numtodsinterval( m )
• 日付文字として正しいかチェックする ⇒ is_date ( s , f, fx )
• 文字列をタイムスタンプ型に変換する ⇒ to_timestamp ( s, f, s )
• システム変更番号を日付に変換する ⇒ scn_to_timestamp( n )
• TO_NUMBER 関数
• TO_CHAR 関数（日付）

関連事項

• 日時・日付・期間 リテラル
• ORA-01846: 指定した曜日が無効です。
• ORA-01865: 年代が無効です。
• NLS_CALENDAR を新元号 令和に対応させる