日付用の書式モデル

主な用途は TO_CHAR(日付)TO_DATETO_TIMESTAMP 関数などにおいて日付、時刻用のフォーマット文字列として使用する。
数値書式

書式 修飾子

修飾子FM とFX を使用して、符号の表示、0や空白の埋め方および書式検査の制御が可能。
※ TO_CHAR、TO_datetime(TO_DATE、TO_TIMESTAMP、etc) ファンクションで利用できる。

書式説明
FM任意の位置に指定し、以後の文字、数値、日時の空白や0を圧縮する。
(Fill Mode:埋め込みモード ≠ Pad 、「圧縮埋め込み」モードと呼んだ方がわかりやすそう。)
FX任意の位置に指定し、以後の書式を厳密なものとする。
(Formar Exact:厳密な書式)書式に適合しない場合にはエラーとなる。
同じ修飾子を複数記述している場合、修飾子の有効化/無効化/有効化/… のトグルとなる。

日付書式

日付書式の最大長は 22 文字*1、フォーマットのデフォルトは データ型 により NLS_DATE_FORMAT、NLS_TIMESTAMP_FORMAT 、NLS_TIMESTAMP_TZ_FORMAT 初期化パラメータが使用される。 また、NLS_DATE_LANGUAGE は月や曜日に影響をあたえる。

セッションでの設定例

ALTER SESSION SET NLS_DATE_FORMAT = 'RR/MM/DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'RR/MM/DD HH24:MI:SSXFF';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT= 'RR/MM/DD HH24:MI:SSXFF3 TZR';
ALTER SESSION SET NLS_DATE_LANGUAGE = 'AMERICAN';

期間型の書式について:期間型には、まだ書式モデルが存在しない。EXTRACT を使用するしかないとある。(Oracle 10g 時点)

書式TO_datetime
で指定できるか(※1)
FM修飾子の影響説明
紀元AD(A.D.)YESなし西暦 1年(含む)以降の場合 '西暦'、西暦 1年より前(含まない)の場合 '紀元前' となる (日本語の場合、ドットあり、なしで違いはない)
BC(B.C.)
世紀CCN/A(※2)あり西暦 2000 年は 20 、2001年は 21、1年 は 01 (SCCは符号つき)
SCC
時代名EOracle 11g 以降
YES
なし時代名の略称(M = 明治,T = 大正,S = 昭和,H = 平成など)、使用例 EYY
EE時代名の完全な名称(明治、大正、昭和、平成など)、使用例 EEYY
YYYYYESあり4 桁の西暦 (SYYYYは符号つき)
SYYYY
YYY年、下 1~3桁(SY,SYY,SYYYは使えない)
YY
Y
RR2000年問題で登場した書式(ROUNDの略)、システム日付が 2049年*2までは '50〜99年 は 1950〜1999年に変換される
RRRR
IYYYNO(※2)ISO 8601 「ISO week-numbering year」 の年(※3) 4桁
Wikipedia - ISO 8601 の 「年と週と曜日」 YYYY-Www-D)の YYYY の部分
週暦による差だけでなく、西暦 1年の前年では 'IYYY' の場合 '0000' となり 'SYYYY' では '-0001' となる
補足:ISO 8601-2004 において 0未満 または 9999を超える年の表現には送/受の双方で取り決めた拡張が必要とされる
IYYISO 8601 規格 「ISO week-numbering year(年と週と曜日)」での年、下 1~3桁(符号は含まれない)
IY
I
SYEARN/Aなし西暦 2000 年は 'TWO THOUSAND'、2020 年は 'TWENTY TWENTY' Oracle 12.2 時点ではどの言語環境でも英語表記になる(SYEARは符号つき)
YEAR
QN/Aなし四半期 1月~3月まで 1、4月~6月まで 2、7月~9月まで 3、10月~12月まで 4
MMYESあり月を 2 桁であらわす (00 〜 12)
MON月を省略名であらわす(Oracle 10g 以降 は '1月 '〜'12月')。NLS_DATE_LANGUAGE などの初期化パラメータにより ('JAN' 〜 'DEC') に変化する
最大桁数でフォーマットされる(後続スペースの付与)
MONTH月をフルスペルであらわす 日本語では関係なし('1月 '〜'12月')
RMローマ数字表記の月('I '〜'XII ') 特殊文字ではなく最大桁数(4) でフォーマットされる(後続スペースの付与)
曜日DYYES曜日の省略形('日'〜'土')、Dy, dy と大文字小文字によっては結果が変化するテリトリもある*3
DAY曜日('日曜日'〜'土曜日')、Day, day も テリトリによっては変化する *4
DDYES日にちを 2 桁であらわす ( 01 〜 31)
時刻AM(A.M.)YESなし?日時の値により '午前' または '午後' (日本語の場合、ドットあり、なしで違いはない)
PM(P.M.)Oracle 11g 以降
YES*5
HHYESあり時 12 時間表記( 01 〜 12 )
HH12Oracle 11g 以降
YES
HH24YES時 24 時間表記( 00 〜 24 )
MI分 ( 00 〜 59 )
SS秒 ( 00 〜 59 )
FF[1..9]なし秒の小数部 1 桁〜 9桁、(例) FF3, FF6, FF9 / F は femto(1.0e-15) の略か?
タイム ゾーンTZRYESなしタイムゾーン 地域名
TZDタイムゾーン 省略名
TZHありタイムゾーン オフセット「時」要素
TZMタイムゾーン オフセット「分」要素
ある基準日からの換算
ユリウスJYESなし西暦 -4712 年1 月1 日から経過した日数 (例) 2005年1月1日=2453372日
IWN/AありISO 8601 規格での年の初めからの週( 01 〜 53 )
WW年の初めからの週( 01 〜 53 )
Wなし月の初めからの週、1日から7日が 1週目( 1 〜 5 )
日(週)DYES週の初めからの日にち( 1 〜 7 ) 日本(米国も)のテリトリでは 日曜日が週初め(=1)、月曜が週初め(=1)の国もある(ISO、EU 各国など)
DDDYESあり年の初めからの日にち( 001 〜 366 )
SSSSSYES日にち初めからの秒数( 00000 〜 86399 )
区切り文字
XYESなしローカル基数文字。秒と1秒未満との区切り。日本だとドット(.)
-/,.;:YESなしハイフン(-),スラッシュ(/),カンマ(,)、ドット(.)、セミコロン(;),コロン(:),
二重引用符(")で囲んだ文字列 "text"
スペースなしマニュアルには特記はされていないが、スペース(' ')もデリミタとしては良く使われている
開発者向けの組み込み書式?
NLS_TERRITORY と NLS_LANGUAGE によって結果が変化する
短い日付DS Oracle 10gYES-JAPAN、JAPANESE 環境では 'RRRR/MM/DD' のエイリアス相当
年、月、日の並びや項目のセパレーターが変化項目
TS 以外と混在させないこと(少なくとも先行記述した FM はこの書式を経由するとリセットされる)
長い日付DL Oracle 10g-JAPAN、JAPANESE 環境では 'FMYYYY"年"MM"月"DD"日" DAY' のエイリアス相当と思われる。
NLS_DATE_FORMAT の設定値でも変化すると記述されているが複数の国、言語でも固有の変化がなく仕様がわからない(Oracle 12.2 時点)
TS 以外と混在させないこと(少なくとも先行記述した FM はこの書式を経由するとリセットされる)
短い時間TS Oracle 10g-JAPAN、JAPANESE 環境では 'FMHH24:FMMI:SS' のエイリアス相当
「時」フィールドだけが FillMode の書式。24時間表記、12時間表記、AM/PM が変化項目で区切り文字は変化しない(ドイツやフィンランドは時刻がドット(.)区切りらしいので)
DL または DS 以外は混在させないこと(少なくとも先行記述した FM はこの書式を経由するとリセットされる)

(※1) 「TO_datetimeで指定できるか」について

SQL 言語リファレンス上では 「TO_*日時ファンクションで指定可能かどうか」は Oracle 10g と 11g では適用範囲が大きく異なるので互換性のある情報としては扱えない。Oracle 10g 以前の情報は、TO_CHAR では使えるが別の関数の何で使えないのか?使用はできるが未サポートなのか、など表記のあいまいさを検証して書き起こせないので記載はしていません。

Oracle 11 以降

  • 「TO_* 日時ファンクションは、TO_DATE、TO_TIMESTAMP および TO_TIMESTAMP_TZ です。」

Oracle 10g 以前

  • 「TO_* 日時ファンクション(TO_CHAR、TO_DATE、TO_TIMESTAMP、TO_TIMESTAMP_TZ、TO_YMINTERVAL および TO_DSINTERVAL)」

違いは、TO_DATE、TO_TIMESTAMP、TO_TIMESTAMP_TZ は特定の日付型、日時型への厳格な変換だが TO_CHAR、TO_YMINTERVAL 、TO_YMINTERVAL は期間の意味を含むことできる。世紀、期、週だけでは自、至があるので日付型へと再変換できないと解釈するのか…?
参考 ⇒ TO_DATE は日付、時間は不足しているフィールドを自動で補完する

(※2) N/A と NO

マニュアルには無い分類です(合理的な分類はしないほうがよいかも)

  • N/A (適用外)
    N/Aと表記したのはマニュアルでは YES 以外すべて「空白」(10g 以前は NO 明記)なので「?」と思うため。
    空白に該当する書式のほどんどが複数日付を示し変換でできないことを察することはできる(補完ルールを適用すると…だが)
  • NO(使用不可)
    NO と表記したのは日付変換において 日付フィールドの補完ルール に従えば日付型に変換できても良いのでは?と感じたもの('D' 書式を YES にしているので)

つまり TO_DATE('2000-01', 'IYYY-IW') ⇒ '2000-01-03 00:00:00' 変換はできて欲しいと思います。

(※3) 「ISO '週歴' での年」

「ISO '週歴' での年」(英:ISO week-numbering year に該当する日本語の固有名はわからない)は「カレンダーの年 (Calendar dates)」とは異なる。
「ISO 週歴での年」の公式な規定は「ある年の最初の木曜日を含む「週」がその年の第 1週である」
定義の前提として ISO 8601 の「週」とは月曜から始まり日曜で終わる(日本と違う)

例) 2000年 1月 1日(土)の 「ISO 週歴での年」は 1999年。1月3日からが 2000年となる。

2000年 1月(ISO 8601)
272829303112← 1999年の週がおわる
345789← 2000年の週はじまり
 
2000年 1月(日本のカレンダー)
2627282930311
234578
オレンジ色 の部分は 1999 年

ISO 8601 関連の参考

 


書式の検証

書式例 (空白をアンダーバー、アンダースコア(_)にて表記)
TO_CHAR( date1, 'fmt') , TO_CHAR( date1, 'FMfmt')
TO_CHAR( date2, 'fmt') , TO_CHAR( date2, 'FMfmt')

date0001
TO_TIMESTAMP('+0001/02/03 04:05:06.012345678','SYYYY/MM/DD HH24:MI:SS.FF9')
date2005
TO_TIMESTAMP('+2005/12/31 23:59.59.100000000','SYYYY/MM/DD HH24:MI:SS.FF9')
とする。
書式
fmt
入力
date
出力
書式修飾子
なし
出力
書式修飾子
FM 使用
補足
TO_CHAR( date, 'YYYY')date0001'0001''1'
date2005'2005''2005'
TO_CHAR( date, 'SYYYY')date0001'_0001''1'
date2005'_2005''2005'
TO_CHAR( date, 'MM')date0001'02''2'
date2005'12''12'
TO_CHAR( date, 'DD')date0001'03''3'
date2005'31''31'
TO_CHAR( date, 'YYYY/MM/DD')date0001'01/02/03''1/2/3'各要素が個別にサプレスされる
date2005'2005/12/31''2005/12/31'
TO_CHAR( date, 'MON')date0001'1月_''1月'後続スペースがサプレスされる
date2005'12月''12月'
TO_CHAR( date, 'AMHH)date0001'午前04''午前4'
date2005'午後11''午後11'
TO_CHAR( date, 'HH24:MI:SS')date0001'04:05:06''4:5:6'
date2005'23:59:59''23:59:59'
TO_CHAR( date, 'FF9')date0001'012345678''012345678'FMによって変化しない
date2005'000000000''000000000'
TO_CHAR( date, 'FF3')date0001'0123''0123'
date2005'100''100'後続ゼロに変化なし
TO_CHAR( date, 'FF')date0001'012345678''012345678'FF9と同じ結果
date2005'000000000''000000000'
TO_CHAR( date, 'SSSSS')date0001'14706''14706''00000'
date2005'86399''86399'
-'00000''0'0時0分0秒の場合

関連事項

日本オラクル
■ 日本オラクル 株式会社
■ オラクルマスター資格 (オラクルマスターとは
■ Oracle Web セミナー

*1 どのような表現で 22 文字までなのか不明。40 バイト近辺で不具合が起きることはある。
*2 当然 2000年に限ったことではないが長くなるので省略
*3 日本語、英語の場合は、どの曜日も同じ長さなのでFMとは無関係、日本語には大文字小文字表記( Dy , dy )も関係ない
*4 日本語の場合は、どの曜日も同じ長さなのでFMとは無関係、大文字小文字表記( Day , day )も関係ない
*5 9i/10g環境で未検証 AM との不一致は Oracle 9i/10g マニュアルの誤記なのかもしれない