メニューボタン
IBMi海外記事2019.03.14

2つの日付間の日数を簡単に算出する

Ted Holt 著
Pick Up

こんにちは、Ted:
私の会社のデータベースでは、日付を一般的なCYYMMDD形式で7桁のパック10進数の値として格納しています。
2018年のうちに、2つの日付の間の日数を報告するSQLクエリーを書いたのですが、2019年の日付を使用し始めたとたんに正確な計算が行われなくなりました。2つの日付間の差分を算出する正しい方法を教えてもらえないでしょうか。
-Becki


Beckiさんのおっしゃるような「正しい方法」が存在するのかどうかは分かりませんが、必要とされる計算を行う方法をご紹介することは可能です。

SQLには、そのような問題に対処するいくつかの便利な組み込み関数があります。

DAYS関数は、日付の序数値を返します。ある日付の序数値を、もう一方の日付の序数値から差し引けば、経過日数を得ることができます。

DAYSには日付を指定する必要があるため、7桁の数値を日付データ型に変換する必要があります。

そのためには、TIMESTAMP_FORMAT関数またはそのエイリアス、TO_DATEを使用します。

そして、TIMESTAMP_FORMATには文字引数を指定する必要があるため、CHARを使用して、数値を文字テキストに変換します。

まず、テスト データを使用してみましょう。

declare global temporary table DateDiffs
  (Key   dec(3), DueDate   dec(7), ShipDate dec(7));
  
insert into session.DateDiffs values
( 1 , 1181231 , 1190101),
( 2 , 1190101 , 1190101),
( 3 , 1190101 , 1181231),
( 4 , 1180506 , 1190506),
( 5 , 1190506 , 1180506),
( 6 , 1190201 , 1190301),
( 7 , 1200201 , 1200301);

以下は、CHAR、TIMESTAMP_FORMAT、およびDAYS関数を使用したクエリーです。

select dd.*, 
       days(timestamp_format(char(ShipDate+19000000),'YYYYMMDD')) -
       days(timestamp_format(char(DueDate +19000000),'YYYYMMDD'))  
from session.DateDiffs as dd
キー 期限 出荷日 遅延日数
1 1181231 1190101 1
2 1190101 1190101 0
3 1190101 1181231 -1
4 1180506 1190506 365
5 1190506 1180506 -365
6 1190201 1190301 28
7 1200201 1200301 29

正しく計算されているでしょうか。間違いはないと思われます。

私が作成したFMTDATE関数を使用すると、このクエリーを少しシンプルにすることができます。

select dd.*, 
       days(date(fmtdate(ShipDate,'CYMD', 'ISO-'))) - 
       days(date(fmtdate(DueDate, 'CYMD', 'ISO-'))) 
from session.DateDiffs as dd;

ただし、私があなたの立場だったら、もう一歩足を踏み出してみることでしょう。

私だったら、日付計算を実行する関数を書きます。

それらの複雑なネスト状の組み込み関数をすべて取り除いてくれるものです。

関数の作成は難しくありません。まず、2つの日付の間の日数を返すRPGサブプロシージャーを書くことから始めます。

**free
ctl-opt  actgrp(*new);

dcl-s  FromDate      packed(7)  inz(1190101);
dcl-s  ThruDate      packed(7)  inz(1190201);
dcl-s  Days          packed(7);

*inlr = *on;
Days = Days_after (ThruDate: FromDate);
return;

dcl-proc  Days_after;
   dcl-pi Days_after packed(7);
      ThruDate       packed(7)  const;
      FromDate       packed(7)  const;
   end-pi Days_after;

   monitor;
      return %diff( %date(ThruDate: *cymd):
                    %date(FromDate: *cymd): *days);
   on-error;
      // fixme
   endmon;

end-proc;

重要なのはDAYS_AFTERサブプロシージャーです。

それよりの前のメインのコードは、テストのためだけのものです。

後で削除すればよいでしょう。

このサブプロシージャーが適切な結果をもたらすことを確認したら、SQLで必要となるパラメーター リストを追加します。

**free
ctl-opt  actgrp(*new);

dcl-s  FromDate      packed(7)  inz(1190101);
dcl-s  ThruDate      packed(7)  inz(1190201);
dcl-s  Days          packed(7);

dcl-s ThruDateNull   int      (   5);
dcl-s FromDateNull   int      (   5);
dcl-s ReturnValNull  int      (   5);
dcl-s ReturnState    char     (   5);
dcl-s FunctionName   varchar  ( 517);
dcl-s SpecificName   varchar  ( 128);
dcl-s MessageText    varchar  (1000);

*inlr = *on;
Days_after (ThruDate: FromDate: Days:
            ThruDateNull: FromDateNull: ReturnValNull:
            ReturnState:
            FunctionName: SpecificName: MessageText);
return;

dcl-proc  Days_after;
   dcl-pi Days_after;
      ThruDate       packed   (   7);
      FromDate       packed   (   7);
      ReturnValue    packed   (   7);
      ThruDateNull   int      (   5);
      FromDateNull   int      (   5);
      ReturnValNull  int      (   5);
      ReturnState    char     (   5);
      FunctionName   varchar  ( 517);
      SpecificName   varchar  ( 128);
      MessageText    varchar  (1000);
   end-pi Days_after;

   ReturnState = *zeros;
   clear MessageText;
   monitor;
      ReturnValue = %diff( %date(ThruDate: *cymd):
                           %date(FromDate: *cymd): *days);
   on-error;
      ReturnState = '85555';
      MessageText = 'A decimal value could not be converted to a date.';
   endmon;

end-proc;

SQLパラメーター スタイルは、エラーがあった場合に簡単にSQL状態を設定する方法を提供します。

ON-ERRORの下に追加したエラー処理コードに注目してください。

ここではSQL状態を02000より大きい値に設定し、メッセージ テキストを指定しています。

サブプロシージャーが新たなパラメーター リストで正常に動作したら、メインの計算コードを削除して、このコードをモジュールに変換します。

**free                                                             
ctl-opt   nomain;                                                  
                                                                   
dcl-proc  Days_after     export;                                   
   dcl-pi Days_after;                                              
      ThruDate       packed   (   7);                              
      FromDate       packed   (   7);                              
      ReturnValue    packed   (   7);                              
      ThruDateNull   int      (   5);                              
      FromDateNull   int      (   5);                              
      ReturnValNull  int      (   5);                              
      ReturnState    char     (   5);                              
      FunctionName   varchar  ( 517);                              
      SpecificName   varchar  ( 128);                              
      MessageText    varchar  (1000);                              
   end-pi Days_after;                                              
                                                                   
   ReturnState = *zeros;                                           
   ReturnValNull = *zeros;                                         
   clear MessageText;                                              
   monitor;                                                        
      ReturnValue = %diff( %date(ThruDate: *cymd):                 
                           %date(FromDate: *cymd): *days);         
   on-error;                                                                 
      ReturnState = '85555';                                                 
      MessageText = 'A decimal value could not be converted to a date.';     
   endmon;                                                                   
                                                                             
end-proc;   

モジュールおよびサービス プログラムを作成します。

CRTRPGMOD MODULE(xxx/DAYS_AFTER)
          SRCFILE(xxx/QRPGLESRC)
          SRCMBR(DAYS_AFTER)
CRTSRVPGM SRVPGM(xxx/DAYS_AFTER) 
          MODULE(xxx/DAYS_AFTER) 
          EXPORT(*ALL)

サービス プログラムでサブプロシージャーを実行する関数を作成します。

create or replace function xxx.days_after
   (ThruDate  dec(7), FromDate dec(7)) 
   returns dec(7)
   language rpgle
   parameter style sql
   deterministic
   returns null on null input
   external name 'XXX/DAYS_AFTER(DAYS_AFTER)'

これで、作成した便利な関数を使用できるようになります。

select dd.*, days_after(ShipDate, DueDate) as DaysLate
  from session.DateDiffs as dd

いかがでしょうか。

RPGサブプロシージャーを書くことができるのであれば(できるのですが)、SQL関数を書くことができるというわけです。

サイト内全文検索

PAGE TOP