日付用の書式モデル

主な用途は TO_CHARTO_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_DATE_LANGUAGE は月や曜日に影響をあたえる。

セッションでの設定例

ALTER SESSION SET NLS_DATE_FORMAT = 'RR/MM/DD HH24:MI:SS';
ALTER SESSION SET NLS_DATE_LANGUAGE = 'AMERICAN';

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

書式説明
時代名E時代名の略称(M = 明治,T = 大正,S = 昭和,H = 平成など)、使用例 EYY
EE時代名の完全な名称(明治、大正、昭和、平成など)、使用例 EEYY
注意:マニュアル上では E/EE 書式は TO_* の日時ファンクションでの使用が「いいえ」になっている。
TO_DATE 単独の 和暦変換 はサポート外と思われる
YYYY4 桁の西暦 (Sは符号つき) ※
SYYYY
Y,YYY
SY,YYY
Y〜YYY1 〜 3 桁の年(Sは使えない)※
RR2000年問題で登場した書式(ROUNDの略)、システム日付が2049年*2までは '50〜99年 は 1950〜1999年に変換される。
RRRR
MM月を 2 桁であらわす (00 〜 12)※
MON月を省略名であらわす('1月 '〜'12月':Oracle 10g)。NLS_DATE_LANGUAGE などの初期化パラメータにより ('JAN' 〜 'DEC') に変化する。
最大桁数でフォーマットされる(後続スペースの付与)。※
MONTH月をフルスペルであらわす。日本語では関係なし('1月 '〜'12月')※
DY曜日の省略形('日'〜'土')、Dy, dy と大文字小文字によっては結果が変化するテリトリもある。※*3
DAY曜日('日曜日'〜'土曜日')、Day, day も テリトリによっては変化する。※*4
DD日にちを 2 桁であらわす ('01' 〜 '31')※
時刻HH時 12 時間表記( 01 〜 12 )※
HH24時 24 時間表記( 00 〜 24 )※
AM(A.M.)'午前'、'午後' をあらわす。(日本語の場合、ドットあり、なしで違いはない)
MI分 ( 00 〜 59 )※
SS秒 ( 00 〜 59 )※
FF[1..9]秒の小数部 1 桁〜 9桁、(例) FF3, FF6, FF9 / F は femto(1.0e-15) の略か?
タイム ゾーンTZRタイムゾーン 地域名
TZDタイムゾーン 省略名
TZHタイムゾーン オフセット「時」要素
TZMタイムゾーン オフセット「分」要素
ある基準日からの換算
ユリウスJ紀元前4712 年1 月1 日から経過した日数 (例) 2005年1月1日=2453372日
WW年の初めからの週( 01 〜 53 )※
W月の初めからの週、1日から7日が 1週目( 1 〜 5 )
曜日D週の初めからの日にち( 1 〜 7 ) 日本のテリトリでは 日曜日が週初め(=1)、月曜が週初め(=1)の国もある(ドイツなど)
DDD年の初めからの日にち( 001 〜 365 )※
SSSSS日にち初めからの秒数( 00000 〜 86399 )※
区切り文字
-/,.;:ハイフン(-),スラッシュ(/),カンマ(,)、ドット(.)、セミコロン(;),コロン(:),
二重引用符(")で囲んだ文字列 "text" … 使用しない方がよい。
二重引用符は使用しない方がよい理由
スペースマニュアルには記載されていないが、スペース(' ')もフィールドデリミタとしては良く使われている。
本来は二重引用符で囲む必要があるのかも?(少なくとも 10g では囲まない方が良い)

() FM修飾子の指定によって変化する。

 

書式の検証

書式例 (空白をアンダーバー、アンダースコア(_)にて表記)
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秒の場合

二重引用符は使用しない方がよい理由

ORA-00600:[17182] (17182はメモリ不正をあらわすIDらしい) もしくは、接続切断(接続サーバープロセスがコアダンプ)が発生する。
(Linux + 10.1.0.2.0) 10.2.0.1.0 も同様
書式パーサの不備によるものかもしれない。(アラートログにはスタックアンダーフローとある。)

ある二重引用符を用いた書式を実行した後に、二重引用符の挙動が変化する。その状態になると 単純なケースでは二重引用符を用いて次の書式までが 40 バイト以上になる場合に問題が発生。 二重引用符を 2 ヵ所以上に配置すると、40 バイトの許容範囲は低下し CORE を出力、切断された。
いろいろと試してみればみるほど二重引用符での区切り文字は、控えた方がよいと確信する結果となった。
また、エラーが発生しなくても二重引用符使用時には、出力桁数が書式指定どおり出力しない問題も発生した。
(KROWNにあるのだろうか?、一般向けには公開はされていない模様)

TO_CHAR(SYSDATE,'YYYYYYYYYYYYY.............YYYY') ⇒ '20052005 ...2005'   ... OK (限界は未検証)
TO_CHAR(SYSDATE,'"....39バイト..."YYYY/MM') ⇒ '0123456789....7892005/07' ... OK
TO_CHAR(SYSDATE,'"....40バイト..."YYYY') ⇒  ... NG
TO_CHAR(SYSDATE,'"....30バイト..."9 バイト分スペースYYYY') ⇒  '01...789 ... 2005/07'... OK
TO_CHAR(SYSDATE,'"....30バイト..."10 バイトスペースYYYY') ⇒  ... NG

関連事項

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

*1 どのような表現で 22 文字までなのか不明。40 バイト近辺で不具合が起きることはある。