テクノロジー：PL/SQL

PL/SQLでの日付の操作
Steven Feuerstein著

PL/SQLの理解と利用に関するシリーズ記事のパート5

このPL/SQL入門シリーズの前回までの記事では、PL/SQLベースのアプリケーションで文字列と数値を操作する方法について取り上げました。間違いなく、文字列と数値は重要ですが、それに加えて日付を使用しないアプリケーションはほぼ存在しません。イベントの実施日、生年月日などを追跡する必要があるからです。

そのため、次のことがかなり頻繁に必要になります。 

  • 日付の変数と定数を宣言する

  • 日付の値を表示、変更するための組込みファンクションを使用する

  • 日付に対して計算を実行する 

また、日付は、文字列や数値よりもかなり複雑なデータ型です。複数の要素（年、月、日、時など）で構成され、有効な日付に関する多くのルールが存在します。この記事では、PL/SQLプログラムで日付の操作を始めるために必要となるすべての情報を提供します。

PL/SQLでの日付、タイムスタンプ、時間隔

ほとんどのアプリケーションで、日付や時刻を保管し、操作する必要があります。文字列や数値とは異なり、日付は非常に複雑です。高度な書式付きのデータであり、さらに有効値や有効計算を判定するための多くのルールが存在します（うるう日とうるう年、サマータイムへの変更、法定休日や会社規定の休日、日付の範囲など）。

幸いにも、Oracle DatabaseとPL/SQLは、内部的な標準形式で日付と時刻の両方を格納する正確な日付/時刻データ型セットを提供しています。また、日付と時刻を操作するための非常に多くの組込みファンクションもあります。

日付と時刻の操作に使用できるデータ型は、次の3つです。 

  • DATE—日付と時刻が格納されるデータ型。この日付と時刻は秒に解決されます。タイムゾーンは含まれません。DATEは、Oracleアプリケーションで日付を扱うために非常によく使用される、もっとも古くからあるデータ型です。

  • TIMESTAMP—タイムスタンプは日付に似ていますが、おもな違いは次の2つです。(1) もっとも近い10億分の1秒（小数点以下9桁の精度）に解決される時刻を格納し、操作できます。(2) タイムスタンプにはタイムゾーンを関連付けることができ、Oracle Databaseでタイムスタンプを操作する際には、そのタイムゾーンが考慮されます。

  • INTERVAL—DATEとTIMESTAMPでは時間の特定のポイントが記録されますが、INTERVALでは期間が記録されます。年や月、あるいは日や秒の単位で時間隔を指定できます。 

リスト1に、これらのデータ型に基づいて宣言した変数の例を示します。

コード・リスト1：DATE、TIMESTAMP、INTERVALの変数の宣言

DECLARE
   l_today_date        DATE := SYSDATE;
   l_today_timestamp   TIMESTAMP := SYSTIMESTAMP;
   l_today_timetzone   TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP;
   l_interval1         INTERVAL YEAR (4) TO MONTH := '2011-11';
   l_interval2         INTERVAL DAY (2) TO SECOND := '15 00:30:44';
BEGIN
   null;
END;

時間隔や、タイムゾーンを含むタイムスタンプの操作は、非常に複雑になることもありますが、これらの高度な機能を必要とする開発者は比較的少数です。この記事では、中心的なDATE型とTIMESTAMP型に焦点を当て、さらに非常によく使用される組込みファンクションについても説明します。

データ型の選択：このように数多くの型がある中で、使用する日付/時刻データ型をどのように判断すれば良いでしょうか。次にいくつかのガイドラインを挙げます。 

  • 時間を小数秒の単位まで追跡する必要がある場合は、TIMESTAMP型のいずれかの形式を使用する。

  • 一般的には、DATEの代わりにTIMESTAMPを使用できる。精度が1秒未満のデータを含まないタイムスタンプはDATEデータ型と同様に7バイトの記憶域を占める。1秒未満のデータを含むタイムスタンプは、11バイトの記憶域を占める。

  • データ入力時のセッション・タイムゾーンを追跡する必要がある場合は、TIMESTAMP WITH TIME ZONEを使用する。

  • データベースとセッション・タイムゾーンで自動的に時刻を変換する場合は、TIMESTAMP WITH LOCAL TIME ZONEを使用する。

  • TIMESTAMPデータ型が導入される前に開発された既存のアプリケーションとの互換性を維持する必要がある場合は、DATEを使用する。

  • 基盤となるデータベース表に対応するデータ型か、少なくともそれらと互換性のあるデータ型をPL/SQLコード内で使用する。たとえば、表からTIMESTAMP値を読み取ってDATE変数に格納すると、情報が失われる可能性がある（この場合は小数秒と、おそらくはタイムゾーンが失われる）ため、その前によく検討する。

現在の日付と時刻の取得：PL/SQL開発者は、現在の日付と時刻を取得して操作する必要がよくあります。ほとんどの開発者は従来のSYSDATEファンクションを使用しますが、現在のOracle Databaseでは、現在の日付と時刻に関する各種情報を取得できる複数のファンクションを提供しています。これらのファンクションについて、表1に示します。

ファンクション タイムゾーン 戻り値のデータ型
CURRENT_DATE セッション DATE
CURRENT_TIMESTAMP セッション TIMESTAMP WITH TIME ZONE
LOCALTIMESTAMP セッション TIMESTAMP
SYSDATE データベース・サーバー DATE
SYSTIMESTAMP データベース・サーバー TIMESTAMP WITH TIME ZONE

表1：現在の日付と時刻を操作するためのSYSDATEとその他のオプション

リスト2では、SYSDATEとSYSTIMESTAMPのコールの戻り値が表示されます。

コード・リスト2：SYSDATEとSYSTIMESTAMPのコールおよびその戻り値

BEGIN
  DBMS_OUTPUT.put_line (SYSDATE);
  DBMS_OUTPUT.put_line (SYSTIMESTAMP);
  DBMS_OUTPUT.put_line (SYSDATE - SYSTIMESTAMP);
END;
/

出力結果は次のようになります。

07-AUG-11
07-AUG-11 08.46.16.379000000 AM -05:00
-000000000 00:00:00.379000000

DBMS_OUTPUT.PUT_LINEに日付とタイムスタンプを渡しているため、これらの値は、Oracle Databaseにより暗黙的に文字列に変換されます。その際に、データベースまたはセッションのデフォルトの書式マスクが使用されます（デフォルトの書式マスクは、各国語設定のNLS_DATE_FORMATパラメータによって指定します）。Oracle

Databaseのデフォルトのインストールでは、デフォルトのDATE書式がDD-MON-YYYYに設定されます。デフォルトのTIMESTAMP書式には、日付オフセットとタイムゾーン・オフセットの両方が含まれます。

注目すべき点は、日付の計算を実行できることです。コード・リスト2では、SYSDATEの戻り値からSYSTIMESTAMPの戻り値を減算しています。その結果は、ゼロに非常に近い（ただし等しくはない）時間隔になります。

日付から文字列、文字列から日付への変換：

数値に対するTO_CHARと同様に、別のバージョンのTO_CHARファンクションを使用して、日付またはタイムスタンプを文字列に変換できます。また、やはり数値と同様に、Oracle Databaseでは、必要としている表示方法へと文字列を調整するための多くの書式要素を提供しています。次にいくつかの例を挙げます。 

  • 書式マスクなしでTO_CHARを使用します。書式マスクを指定しない場合、TO_CHARによって返される文字列は、Oracle Databaseで暗黙的な変換が実行された場合に返される文字列と同じです。 

    BEGIN
   DBMS_OUTPUT.put_line (
     TO_CHAR (SYSDATE));
   DBMS_OUTPUT.put_line (
     TO_CHAR (SYSTIMESTAMP));
END;
/ 
07-AUG-11
07-AUG-11 08.55.00.470000000 AM -05:00

    •   

  • TO_CHARを使用してその日付の曜日と月のフルネームを表示します。 

    BEGIN
   DBMS_OUTPUT.put_line (
TO_CHAR (SYSDATE, 
'Day, DDth Month YYYY'));
END;
/
Sunday   , 07TH August    2011

    注：これらの名前の表示に使用する言語は、NLS_DATE_LANGUAGE設定により定義します。この設定は、次のようにTO_CHARコールの第3引数に指定することもできます。

    BEGIN
  DBMS_OUTPUT.put_line (
    TO_CHAR (SYSDATE, 
'Day, DDth Month YYYY', 
'NLS_DATE_LANGUAGE=Spanish'));
END;
/
Domingo  , 07TH Agosto     2011

  • PL/SQL Challengeの正解

    前号のPL/SQLでの数値の操作で出題されたPL/SQL Challengeの各質問の正解は次のとおりです。

    正解1：plch_ceil_and_floorファンクションは常に1または0を返します。ファンクションに渡された数値が整数の場合は0、それ以外の場合は1を返します。

    正解2：(a)と(b)が正しく、(c)は誤りです。

    これらの正解の詳しい説明については、 plsqlchallenge.com にアクセスして登録またはログインし、Play a Quizの「Closed/Taken」タブをクリックするか、 bit.ly/r1SwvP にアクセスしてください。

    TO_CHARを使用してその日付の曜日と月のフルネームを表示しますが、日付の文字列表現では前の例にあるような余白をすべて削除します。Oracle Databaseのデフォルトでは、曜日または月の最大長に合うように文字列が空白でパディングされます。ほとんどの状況で、この余白は必要ありません。Oracle Databaseでは、空白と0（ゼロ）によるパディングを制御するFMという書式要素修飾子を提供しています。次のブロックでは、書式マスクにFMという接頭辞を付けて、（7の前の）0とAugustの後の余白を削除しています。

    BEGIN
  DBMS_OUTPUT.put_line (
     TO_CHAR (SYSDATE, 
'FMDay, DDth Month YYYY'));
END;
/
Sunday, 7TH August 2011

また、次の例のように、日付の一部のみの抽出や日付に関する情報の抽出にも、書式マスクを使用できます。

  • 今日は何番目の四半期か 

    TO_CHAR (SYSDATE, 'Q')
  • 今日の日付は今年の何日目か（1～366） 
    TO_CHAR (SYSDATE, 'DDD')

  • DATE変数の日付と時刻は何か（これは非常によくある要件です。日付のデフォルトの書式マスクには時刻要素が含まれません。つまり、日付を表示するDBMS_OUTPUT.PUT_LINEのコールでは、時刻が省略されます。） 

    BEGIN
  DBMS_OUTPUT.put_line (
    TO_CHAR (SYSDATE, 
'YYYY-MM-DD HH24:MI:SS'));
END;
/

また、EXTRACTを使用して、日付の指定要素の値を抽出して返すこともできます。以下に例を挙げます。

  • 今は何年か 
    EXTRACT (YEAR FROM SYSDATE)

  • 今日の日付の曜日は何か  

    EXTRACT (DAY FROM SYSDATE)

文字列を日付に変換するには、組込みファンクションのTO_DATEまたはTO_TIMESTAMPを使用します。文字列を指定すると、日付またはタイムスタンプが返されます。この際に、セッションのデフォルトの書式マスクが使用されます。 

DECLARE
  l_date   DATE;
BEGIN
  l_date := TO_DATE ('January 12 2011');
END;
/

指定した文字列がデフォルトの書式に合わない場合は、Oracle Databaseの例外が発生します。

DECLARE
  l_date   DATE;
BEGIN
  l_date := TO_DATE ('January 12 2011');
END;
/

ORA-01858: a non-numeric character was 
found where a numeric was expected

TO_DATEのコールに指定するリテラル値がデフォルトの書式に合うことを前提とすべきではありません。長期的に見れば、書式が変更される可能性もあります。そのため、文字列を日付に変換する際には、次のように常に書式を指定してください。

l_date := TO_DATE ('January 12 2011', 
'Month DD YYYY');

日付の切捨て：指定した測定単位にまで日付を切り捨てるには、TRUNC組込みファンクションを使用します。一般的なTRUNCの使用法は、書式マスクを指定しないTRUNC (date)です。この場合のTRUNCでは、単純に時刻が00:00:00に設定されます。また、TRUNCを使用して、指定した期間の初日を簡単に取得することもできます。次にいくつかのTRUNCの例を挙げます。

  • l_dateを今日の日付に設定しますが、時刻は00:00:00に設定します。 

    l_date := TRUNC (SYSDATE); 
  • 指定した日付の月の初日を取得します。 
    l_date := TRUNC (SYSDATE, 'MM');
  • 指定した日付の四半期の初日を取得します。 
    l_date := TRUNC (SYSDATE, 'Q');

    •   

  • 指定した日付の年の初日を取得します。 
    l_date := TRUNC (SYSDATE, 'Y');

日付計算：Oracle Databaseでは、日付とタイムスタンプに対する操作を複数の方法で実行できます。 

  • SYSDATE + 7のように、日付に対して数値を加算または減算した場合、Oracle Databaseではこの数値は日数として扱われる。

  • l_hiredate - SYSDATEのように、ある日付に別の日付を加算または減算できる。

  • ある日付を指定した月数分、あるいは週の別の日付に"移動"するには、組込みファンクションを使用する。 

次に、日付と数値を使用した日付計算の例を挙げます（すべての例で、l_date変数をDATEとしてあらかじめ宣言しています）。

  • ローカル変数を明日の日付に設定します。 

    l_date := SYSDATE + 1;

    •   

  • 1時間戻します。 
    l_date := SYSDATE - 1/24;

    •   

  • 10秒進めます。 
    l_date := SYSDATE + 10 / (60 * 60 * 24);

ある日付に別の日付を加算または減算した結果は、その2つの日付間の日数になります。そのため、次のブロックを実行すると、

掲載元
Oracle Magazine
2012年1/2月
テクノロジー：PL/SQL
  

PL/SQLでの日付の操作
Steven Feuerstein著 Oracle ACE Director
 

PL/SQLの理解と利用に関するシリーズ記事のパート5

このPL/SQL入門シリーズの前回までの記事では、PL/SQLベースのアプリケーションで文字列と数値を操作する方法について取り上げました。間違いなく、文字列と数値は重要ですが、それに加えて日付を使用しないアプリケーションはほぼ存在しません。イベントの実施日、生年月日などを追跡する必要があるからです。

そのため、次のことがかなり頻繁に必要になります。 

日付の変数と定数を宣言する

日付の値を表示、変更するための組込みファンクションを使用する

日付に対して計算を実行する 

また、日付は、文字列や数値よりもかなり複雑なデータ型です。複数の要素（年、月、日、時など）で構成され、有効な日付に関する多くのルールが存在します。この記事では、PL/SQLプログラムで日付の操作を始めるために必要となるすべての情報を提供します。

PL/SQLでの日付、タイムスタンプ、時間隔
ほとんどのアプリケーションで、日付や時刻を保管し、操作する必要があります。文字列や数値とは異なり、日付は非常に複雑です。高度な書式付きのデータであり、さらに有効値や有効計算を判定するための多くのルールが存在します（うるう日とうるう年、サマータイムへの変更、法定休日や会社規定の休日、日付の範囲など）。

幸いにも、Oracle DatabaseとPL/SQLは、内部的な標準形式で日付と時刻の両方を格納する正確な日付/時刻データ型セットを提供しています。また、日付と時刻を操作するための非常に多くの組込みファンクションもあります。

日付と時刻の操作に使用できるデータ型は、次の3つです。 

DATE—日付と時刻が格納されるデータ型。この日付と時刻は秒に解決されます。タイムゾーンは含まれません。DATEは、Oracleアプリケーションで日付を扱うために非常によく使用される、もっとも古くからあるデータ型です。

TIMESTAMP—タイムスタンプは日付に似ていますが、おもな違いは次の2つです。(1) もっとも近い10億分の1秒（小数点以下9桁の精度）に解決される時刻を格納し、操作できます。(2) タイムスタンプにはタイムゾーンを関連付けることができ、Oracle Databaseでタイムスタンプを操作する際には、そのタイムゾーンが考慮されます。

INTERVAL—DATEとTIMESTAMPでは時間の特定のポイントが記録されますが、INTERVALでは期間が記録されます。年や月、あるいは日や秒の単位で時間隔を指定できます。 

リスト1に、これらのデータ型に基づいて宣言した変数の例を示します。

コード・リスト1：DATE、TIMESTAMP、INTERVALの変数の宣言

 

DECLARE
   l_today_date        DATE := SYSDATE;
   l_today_timestamp   TIMESTAMP := SYSTIMESTAMP;
   l_today_timetzone   TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP;
   l_interval1         INTERVAL YEAR (4) TO MONTH := '2011-11';
   l_interval2         INTERVAL DAY (2) TO SECOND := '15 00:30:44';
BEGIN
   null;
END;
 

時間隔や、タイムゾーンを含むタイムスタンプの操作は、非常に複雑になることもありますが、これらの高度な機能を必要とする開発者は比較的少数です。この記事では、中心的なDATE型とTIMESTAMP型に焦点を当て、さらに非常によく使用される組込みファンクションについても説明します。

データ型の選択：このように数多くの型がある中で、使用する日付/時刻データ型をどのように判断すれば良いでしょうか。次にいくつかのガイドラインを挙げます。 

時間を小数秒の単位まで追跡する必要がある場合は、TIMESTAMP型のいずれかの形式を使用する。

一般的には、DATEの代わりにTIMESTAMPを使用できる。精度が1秒未満のデータを含まないタイムスタンプはDATEデータ型と同様に7バイトの記憶域を占める。1秒未満のデータを含むタイムスタンプは、11バイトの記憶域を占める。

データ入力時のセッション・タイムゾーンを追跡する必要がある場合は、TIMESTAMP WITH TIME ZONEを使用する。

データベースとセッション・タイムゾーンで自動的に時刻を変換する場合は、TIMESTAMP WITH LOCAL TIME ZONEを使用する。

TIMESTAMPデータ型が導入される前に開発された既存のアプリケーションとの互換性を維持する必要がある場合は、DATEを使用する。

基盤となるデータベース表に対応するデータ型か、少なくともそれらと互換性のあるデータ型をPL/SQLコード内で使用する。たとえば、表からTIMESTAMP値を読み取ってDATE変数に格納すると、情報が失われる可能性がある（この場合は小数秒と、おそらくはタイムゾーンが失われる）ため、その前によく検討する。

現在の日付と時刻の取得：PL/SQL開発者は、現在の日付と時刻を取得して操作する必要がよくあります。ほとんどの開発者は従来のSYSDATEファンクションを使用しますが、現在のOracle Databaseでは、現在の日付と時刻に関する各種情報を取得できる複数のファンクションを提供しています。これらのファンクションについて、表1に示します。

ファンクション  タイムゾーン  戻り値のデータ型
CURRENT_DATE  セッション  DATE
CURRENT_TIMESTAMP  セッション  TIMESTAMP WITH TIME ZONE
LOCALTIMESTAMP  セッション  TIMESTAMP
SYSDATE  データベース・サーバー  DATE
SYSTIMESTAMP  データベース・サーバー  TIMESTAMP WITH TIME ZONE
表1：現在の日付と時刻を操作するためのSYSDATEとその他のオプション
 

リスト2では、SYSDATEとSYSTIMESTAMPのコールの戻り値が表示されます。

コード・リスト2：SYSDATEとSYSTIMESTAMPのコールおよびその戻り値

 

BEGIN
  DBMS_OUTPUT.put_line (SYSDATE);
  DBMS_OUTPUT.put_line (SYSTIMESTAMP);
  DBMS_OUTPUT.put_line (SYSDATE - SYSTIMESTAMP);
END;
/
 

出力結果は次のようになります。

 

07-AUG-11
07-AUG-11 08.46.16.379000000 AM -05:00
-000000000 00:00:00.379000000
 

DBMS_OUTPUT.PUT_LINEに日付とタイムスタンプを渡しているため、これらの値は、Oracle Databaseにより暗黙的に文字列に変換されます。その際に、データベースまたはセッションのデフォルトの書式マスクが使用されます（デフォルトの書式マスクは、各国語設定のNLS_DATE_FORMATパラメータによって指定します）。Oracle Databaseのデフォルトのインストールでは、デフォルトのDATE書式がDD-MON-YYYYに設定されます。デフォルトのTIMESTAMP書式には、日付オフセットとタイムゾーン・オフセットの両方が含まれます。

注目すべき点は、日付の計算を実行できることです。コード・リスト2では、SYSDATEの戻り値からSYSTIMESTAMPの戻り値を減算しています。その結果は、ゼロに非常に近い（ただし等しくはない）時間隔になります。

日付から文字列、文字列から日付への変換：数値に対するTO_CHARと同様に、別のバージョンのTO_CHARファンクションを使用して、日付またはタイムスタンプを文字列に変換できます。また、やはり数値と同様に、Oracle Databaseでは、必要としている表示方法へと文字列を調整するための多くの書式要素を提供しています。次にいくつかの例を挙げます。 

書式マスクなしでTO_CHARを使用します。書式マスクを指定しない場合、TO_CHARによって返される文字列は、Oracle Databaseで暗黙的な変換が実行された場合に返される文字列と同じです。 

 

BEGIN
   DBMS_OUTPUT.put_line (
     TO_CHAR (SYSDATE));
   DBMS_OUTPUT.put_line (
     TO_CHAR (SYSTIMESTAMP));
END;
/ 
07-AUG-11
07-AUG-11 08.55.00.470000000 AM -05:00
  

TO_CHARを使用してその日付の曜日と月のフルネームを表示します。 

 

BEGIN
   DBMS_OUTPUT.put_line (
TO_CHAR (SYSDATE, 
'Day, DDth Month YYYY'));
END;
/
Sunday   , 07TH August    2011
 

注：これらの名前の表示に使用する言語は、NLS_DATE_LANGUAGE設定により定義します。この設定は、次のようにTO_CHARコールの第3引数に指定することもできます。

 

BEGIN
  DBMS_OUTPUT.put_line (
    TO_CHAR (SYSDATE, 
'Day, DDth Month YYYY', 
'NLS_DATE_LANGUAGE=Spanish'));
END;
/
Domingo  , 07TH Agosto     2011

  

PL/SQL Challengeの正解

前号のPL/SQLでの数値の操作で出題されたPL/SQL Challengeの各質問の正解は次のとおりです。

正解1：plch_ceil_and_floorファンクションは常に1または0を返します。ファンクションに渡された数値が整数の場合は0、それ以外の場合は1を返します。

正解2：(a)と(b)が正しく、(c)は誤りです。

これらの正解の詳しい説明については、plsqlchallenge.comにアクセスして登録またはログインし、Play a Quizの「Closed/Taken」タブをクリックするか、bit.ly/r1SwvPにアクセスしてください。

TO_CHARを使用してその日付の曜日と月のフルネームを表示しますが、日付の文字列表現では前の例にあるような余白をすべて削除します。Oracle Databaseのデフォルトでは、曜日または月の最大長に合うように文字列が空白でパディングされます。ほとんどの状況で、この余白は必要ありません。Oracle Databaseでは、空白と0（ゼロ）によるパディングを制御するFMという書式要素修飾子を提供しています。次のブロックでは、書式マスクにFMという接頭辞を付けて、（7の前の）0とAugustの後の余白を削除しています。

  

BEGIN
  DBMS_OUTPUT.put_line (
     TO_CHAR (SYSDATE, 
'FMDay, DDth Month YYYY'));
END;
/
Sunday, 7TH August 2011
 

また、次の例のように、日付の一部のみの抽出や日付に関する情報の抽出にも、書式マスクを使用できます。

今日は何番目の四半期か
 

TO_CHAR (SYSDATE, 'Q')
 
今日の日付は今年の何日目か（1～366）
 
TO_CHAR (SYSDATE, 'DDD')
 
DATE変数の日付と時刻は何か（これは非常によくある要件です。日付のデフォルトの書式マスクには時刻要素が含まれません。つまり、日付を表示するDBMS_OUTPUT.PUT_LINEのコールでは、時刻が省略されます。） 
 

BEGIN
  DBMS_OUTPUT.put_line (
    TO_CHAR (SYSDATE, 
'YYYY-MM-DD HH24:MI:SS'));
END;
/
 

また、EXTRACTを使用して、日付の指定要素の値を抽出して返すこともできます。以下に例を挙げます。

今は何年か
 

EXTRACT (YEAR FROM SYSDATE)
  

今日の日付の曜日は何か
 

EXTRACT (DAY FROM SYSDATE) 
  

文字列を日付に変換するには、組込みファンクションのTO_DATEまたはTO_TIMESTAMPを使用します。文字列を指定すると、日付またはタイムスタンプが返されます。この際に、セッションのデフォルトの書式マスクが使用されます。 

 

DECLARE
  l_date   DATE;
BEGIN
  l_date := TO_DATE ('12-JAN-2011');
END  ;
 

指定した文字列がデフォルトの書式に合わない場合は、Oracle Databaseの例外が発生します。

 

DECLARE
  l_date   DATE;
BEGIN
  l_date := TO_DATE ('January 12 2011');
END;
/

ORA-01858: a non-numeric character was 
found where a numeric was expected
 

TO_DATEのコールに指定するリテラル値がデフォルトの書式に合うことを前提とすべきではありません。長期的に見れば、書式が変更される可能性もあります。そのため、文字列を日付に変換する際には、次のように常に書式を指定してください。

 

l_date := TO_DATE ('January 12 2011', 
'Month DD YYYY');
 

日付の切捨て：指定した測定単位にまで日付を切り捨てるには、TRUNC組込みファンクションを使用します。一般的なTRUNCの使用法は、書式マスクを指定しないTRUNC (date)です。この場合のTRUNCでは、単純に時刻が00:00:00に設定されます。また、TRUNCを使用して、指定した期間の初日を簡単に取得することもできます。次にいくつかのTRUNCの例を挙げます。

 

l_dateを今日の日付に設定しますが、時刻は00:00:00に設定します。 

 

l_date := TRUNC (SYSDATE); 
 

指定した日付の月の初日を取得します。
  

l_date := TRUNC (SYSDATE, 'MM');
  

指定した日付の四半期の初日を取得します。
 

l_date := TRUNC (SYSDATE, 'Q');
  

指定した日付の年の初日を取得します。
 

l_date := TRUNC (SYSDATE, 'Y');
 

日付計算：Oracle Databaseでは、日付とタイムスタンプに対する操作を複数の方法で実行できます。 

SYSDATE + 7のように、日付に対して数値を加算または減算した場合、Oracle Databaseではこの数値は日数として扱われる。

l_hiredate - SYSDATEのように、ある日付に別の日付を加算または減算できる。

ある日付を指定した月数分、あるいは週の別の日付に"移動"するには、組込みファンクションを使用する。 

次に、日付と数値を使用した日付計算の例を挙げます（すべての例で、l_date変数をDATEとしてあらかじめ宣言しています）。

 

ローカル変数を明日の日付に設定します。 

 

l_date := SYSDATE + 1;
  

1時間戻します。
  

l_date := SYSDATE - 1/24;
  

10秒進めます。
  

l_date := SYSDATE + 10 / (60 * 60 * 24);
 

ある日付に別の日付を加算または減算した結果は、その2つの日付間の日数になります。そのため、次のブロックを実行すると、

 

DECLARE
   l_date1   DATE := SYSDATE;
   l_date2   DATE := SYSDATE + 10;
BEGIN
   DBMS_OUTPUT.put_line (
      l_date2 - l_date1);
   DBMS_OUTPUT.put_line (
      l_date1 - l_date2);
END;

次の出力が返されます。

10
-10

次のステップ

詳細情報

PL/SQLのデータ型

ファンクション

 ダウンロード Oracle Database 11g

 テスト  PL/SQLの知識

その他の記事 PL/SQLの基礎、パート1～4  

また、次のファンクションを使用して、人の年齢を計算できます（ファンクションの唯一の引数として、その人の正しい誕生日を渡すと想定します）。

CREATE OR REPLACE FUNCTION 
your_age (birthdate_in IN DATE)
   RETURN NUMBER
IS
BEGIN
   RETURN SYSDATE - 
          birthdate_in;
END your_age;

Oracle Databaseでは、リクエストした期間分だけ日付を移動する組込みファンクションや日付を検索する組込みファンクションを提供しています。

  • ADD_MONTHS—日付（またはタイムスタンプ）に指定した月数を加算または減算する

  • NEXT_DAY—ファンクションのコールで指定した曜日に該当する最初の日付を返す

  • LAST_DAY—指定した日付が属する月の最終日の日付を返す

次に、これらの組込みファンクションの使用例を示します。

  • 1か月後に移動します。 

     

    l_date := ADD_MONTHS (SYSDATE, 1);

  • 3か月前に移動します。 

    l_date := ADD_MONTHS (SYSDATE, -3); 

    •  

  • 1月の最終日から1か月後に移動します。別の日から1か月前に戻ります。2月の最終日から1か月前に戻ります。これら3つのADD_MONTHSファンクションのコールとその結果について、リスト3に示します。

    コード・リスト3：ADD_MONTHSのコール

    BEGIN
   DBMS_OUTPUT.put_line (
      ADD_MONTHS (TO_DATE ('31-jan-2011', 'DD-MON-YYYY'), 1));
   DBMS_OUTPUT.put_line (
      ADD_MONTHS (TO_DATE ('27-feb-2011', 'DD-MON-YYYY'), -1));
   DBMS_OUTPUT.put_line (
      ADD_MONTHS (TO_DATE ('28-feb-2011', 'DD-MON-YYYY'), -1));
END;

    出力結果は次のようになります。 

    28-FEB-11
27-JAN-11 
31-JAN-11

    リスト3の出力結果にある3つ目の日付に驚くかもしれません。1つ目の日付（2月28日）は妥当です。2月には31日はないため、Oracle Databaseにより月の最終日が返されています。2つ目のADD_MONTHSでは、2月27日から1月27日に移動しています。これはちょうど1か月分の変更です。しかし、3つ目のADD_MONTHSのコールでは、Oracle Databaseで2月28日が月の最終日だと認識されるため、第2引数に指定した月の最終日が返されます。

  • 今日の日付後に来る次の土曜日を探すには、次のように記述します。 

    l_date := NEXT_DAY (SYSDATE, 'SAT');

    -- or

    l_date := NEXT_DAY (SYSDATE, 'SATURDAY');

第2引数には、セッションの日付言語（NLS_DATE_LANGUAGEにより指定）を使用して、フルネームまたは省略名で曜日を記述する必要があります。戻り値の日付は、日付と同じ時間要素で構成されます。

良いプログラムでも良くないことは起きるもの

以上で、文字列、数値、日付などの主要なデータ型を操作するための基礎を身に付けることができました。このシリーズの次の記事では、例外について（特に例外を発生させる方法と例外を処理する方法について）詳しく見ていきます。

クイズにチャレンジ

PL/SQLの基礎に関するそれぞれの記事では、記事の中で説明した情報の知識をテストするクイズを毎回出題しています。このクイズは次に示す他、PL/SQL Challenge（ plsqlchallenge.com ）にも掲載されます。PL/SQL Challengeは、PL/SQL言語のオンライン・クイズを提供するWebサイトです。このOracle Magazineの記事でクイズを読んで回答した場合、正解について次のパートで確認できます。一方、PL/SQL Challengeでクイズに挑戦した場合は、O’Reilly Media（oreilly.com）から電子書籍が当たるチャンスに応募できます。

質問1

Oracle Databaseには、月の最終日の日付を返すファンクションはありますが、初日の日付を返すファンクションはありません。次のうち、初日の日付を返すために使用できるものはどれでしょうか。

  • CREATE OR REPLACE FUNCTION plch_first_day (date_in IN DATE)
   RETURN DATE
IS
BEGIN
   RETURN TRUNC (date_in);
END;
/

 

CREATE OR REPLACE FUNCTION plch_first_day (date_in IN DATE)
   RETURN DATE
IS
BEGIN
   RETURN TRUNC (date_in, 'MM');
END;
/

 

CREATE OR REPLACE FUNCTION plch_first_day (date_in IN DATE)
   RETURN DATE
IS
BEGIN
   RETURN TRUNC (date_in, 'MONTH');
END;
/

 

CREATE OR REPLACE FUNCTION plch_first_day (date_in IN DATE)
   RETURN DATE
IS
BEGIN
   RETURN TO_DATE (TO_CHAR (date_in, 'YYYY-MM')
|| '-01', 'YYYY-MM-DD');
END;
/

質問2

次のような宣言セクションがあるとします。

DECLARE
   c_format   CONSTANT VARCHAR2 (22)
      := 'YYYY-MM-DD HH24:MI:SS' ;
   l_new_year          DATE
      := TO_DATE (
            '2012-01-02 00:00:01'
          ,  c_format);

次のブロックのうち、ブロックの実行後に2012-01-01 00:00:01という日付/時刻が画面に表示されないものはどれでしょうか。

  •  BEGIN DBMS_OUTPUT.put_line ( TO_CHAR ( l_new_year - 24 ,  c_format)); END;

  • BEGIN
   DBMS_OUTPUT.put_line (
      TO_CHAR (
         l_new_year - 24
       ,  c_format));
END;

BEGIN
   DBMS_OUTPUT.put_line (
      TO_CHAR (l_new_year - 1
             ,  c_format));
END;

BEGIN
   DBMS_OUTPUT.put_line (
      TO_CHAR (
           l_new_year
         - 24 * 60 * 60
       ,  c_format));
END;

BEGIN
   DBMS_OUTPUT.put_line (
      TO_CHAR (
           TRUNC (l_new_year)
         - 1
         + 1 / (24 * 60 * 60)
       ,  c_format));
END;

Steven Feuerstein（steven.feuerstein@quest.com）は、Quest SoftwareのPL/SQLエヴァンジェリストです。これまで、Oracle PL/SQLに関する著書（O’Reilly Media）を10冊発行しており、Oracle ACE Directorでもあります。詳細は、stevenfeuerstein.comをご覧ください。

ご意見ご感想をお寄せください。

セールス・チャット

ご購入をお考えですか？

営業担当者に問い合わせる営業担当者とチャット
Cloud Supportのチャット

アカウント/サブスクリプション、プロモーションの問題

チャットを開始
Oracle Supportオプション

テクニカルサポート、またはその他のサポートリクエスト

サポート・オプションを表示