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

日付・日時、期間リテラル

期間リテラル

期間リテラルには複数フィールドを一つで扱う、年月と日時分秒の任意の間の複合型*1の期間リテラル、
または、個々を独立して扱う(年、月、日、時、分、秒) のリテラルがある。
但し Oracle 10g 時点の PL/SQL で使用できるのは YEAR TO DAY と DAY TO SECOND に限られる。 ⇒ 文字・数値 リテラル

データ型として認められている期間リテラル

データ型として認められているものは PL/SQL においても使用できる。

YAER TO MONTH （年〜月)

• 年月( YEAR TO MONTH )

123 年と 11ヵ月
YEAR に精度(3) を指定しないとデフォルトで 2 桁となり ORA-01873: 間隔の先行精度が小さすぎます が発生する。

INTERVAL '123-11' YEAR(3) TO MONTH

1 年と 123ヵ月(月は 0 〜 11 まで)

INTERVAL '1-123' YEAR(1) TO MONTH ⇒ エラー
INTERVAL '135' MONTH(3) なら OK

DAY TO SECOND (日〜秒)

1 日と 1 秒

INTERVAL '1 0:0:1' DAY TO SECOND

データ型として認められていない表記

SQL としては使用できるが　PL/SQL で日付型との算術に使用すると以下のような無関係なエラーが発生する。

例） vDate := vDate  + INTERVAL '23:59:59' HOUR TO SECOND;
PLS-00167: キーワードBULKが間違ったコンテキストで使用されています。

複合型(日〜秒)の任意の組み合わせ

• 日〜秒 ( xxx TO yyy ) xxx, yyy は { DAY | HOUR | MINUTE | SECOND }

1 日と 1 秒

INTERVAL '1 0:0:1' DAY(1) TO SECOND

1000 日と 23 時間 (時〜秒 は 23、または 59まで)

INTERVAL '1000 23' DAY(5) TO HOUR

1 時間と 59 秒

INTERVAL '1:00:59' HOUR TO SECOND

年、月、日、時、分、秒

以下の期間型を加減算して組み合わせることでよりシンプルに記述できることもある。

INTERVAL '1' YEAR
INTERVAL '2' MONTH
INTERVAL '3' DAY
INTERVAL '4' HOUR
INTERVAL '5' MINUTE
INTERVAL '6' SECOND
--
INTERVAL '1' YEAR - INTERVAL '1' SECOND

INTERVAL の使用上の注意
INTERVAL による加減算は末日処理されない。以下の SQL はエラーになる。

SELECT DATE '2000-01-31' + INTERVAL '1' MONTH FROM DUAL ;
--
-- うるう日も同様
SELECT DATE '2000-02-29' + INTERVAL '1' YEAR FROM DUAL ;

末日処理が必要な場合には、ADD_MONTHS 関数を使用する。

日時リテラル

Oracle 9i で追加された 日付、時間に関するリテラルは大きくわけて二種類。
年月日の日付リテラルと時刻までを含む日時リテラルである。
⇒ 別のタイムゾーン日付をセッションタイムゾーン日付に変換する方法

前説： ANSI において DATE は時刻を含まないが Oracle の DATE 型は時刻の情報を含む。
これらの違いは Oracle の歴史と ANSI の標準化内容が、時代から乗り遅れていたことによる影響
(比較的新しい RDBMS は ANSI の定義と同じ データ型 になっていると思います。)

日付リテラル

以下のフォーマットによる、グレゴリオ暦だけが許される。

DATE 'YYYY-MM-DD'

Oracle を扱ってきた技術者にとって Oracle の DATE＝日時、TIMESTAMP＝DATE＋小数点以下の秒という認識が強い。
この表記方法が普及するには、期間型リテラルと同じく(より以上に)時間がかかると思う。

日時リテラル

TIMESTAMP '2005-12-31 22:23:24.123456789'

秒の小数部は省略可能。
書式は厳密に評価される。時刻コンポーネントの省略は許可されない。 (備忘録:マニュアルの読み方次第で誤解しそうなので注意)

使用できない書式の例

SELECT TIMESTAMP '2000-01-01' FROM DUAL 
                 *
行1でエラーが発生しました。:
ORA-01861: リテラルが書式文字列と一致しません
 
SELECT TIMESTAMP '2000-01-01 12:34' FROM DUAL
                 *
行1でエラーが発生しました。:
ORA-01861: リテラルが書式文字列と一致しません
 
SELECT TIMESTAMP '2000/01/01 12:34' FROM DUAL
                 *
行1でエラーが発生しました。:
ORA-01861: リテラルが書式文字列と一致しません
 
SELECT TIMESTAMP '2000-01-01 12.34.56' FROM DUAL
                 *
行1でエラーが発生しました。:
ORA-01861: リテラルが書式文字列と一致しません

タイムゾーン付の表記方法もありますが、日本仕様のシステムでは意味がないので割愛しました。

• 関連事項
    オラクルエラー 対処法 ⇒ ORA-01861: リテラルが書式文字列と一致しません

Oracle 8i 以前の日時リテラル

Oracle 8i 以前に日付リテラルは存在しないが暗黙変換、または変換関数によって記述することは可能。

TO_DATE('2005-12-31 22:23:24', 'YYYY-MM-DD HH24:MI:SS')
  ⇒ 正しい書式
TO_DATE('2005-1-2 3:4:5', 'YYYY:MM.DD:HH24/MI/SS') 
  ⇒ 値と書式は一致しないが正しく変換される。(FM 書式がデフォルトの振る舞い)

FX 書式を使用しなければ日付フィールドのデリミタは、デリミタとして許容されているものであれば寛大に変換してくれる。 ⇒ 書式モデル(日付)

---