2つの日付間の日数を簡単に算出する
こんにちは、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関数を書くことができるというわけです。
