Custom function: YMD_BETWEEN
YMD_BETWEEN
returns an 8-character string that represents the number of years, months, and
days between two dates. The output format is:
01.02.03
·
The 1st
and 2nd characters (01) represent the number of years between the
two dates.
·
The 4th
and 5th characters (02) represent the number of months between the
two dates.
·
The 7th
and 8th characters (03) represent the number of additional days
between the two dates, after allowing for the years and months.
YMD_BETWEEN
takes two required arguments, and one optional argument that will be discussed
later.
·
Argument #1: Any
date
·
Argument #2: Any
date
·
Argument #3:
Calculation type (default = 0)
The
order of the dates in the arguments (earliest, latest) is irrelevant.
YMD_BETWEEN will always return a positive number of time periods, starting with
the earlier date and ending with the later date, irrespective of the order in
which the dates are provided in the arguments. By default, the difference is
computed as of the end of the day for both days.
For
example, the difference between 30Dec2008 and 31Dec2008 is only 1 day, because
by default YMD_BETWEEN assumes that the time is approximately 23:59:59 on each
date.
The code for the YMD_BETWEN function
is included at the end of this blog post.
In
its default mode, YMD_BETWEEN evaluates the dates using the following logic
rules, in order: Once a pair of dates meets a logical condition, the rule for
that condition is applied, and no future logical conditions are tested.
Apply
the rule for the first TRUE condition:
1.
If both of the dates
represent the ending date of their respective months, then zero days are reflected
in the result. Notice that the order of the date arguments doesn't matter.
Example: YMD_BETWEEN('29FEB2008','31AUG2009')
= 01.06.00 (both dates = month end)
Example: YMD_BETWEEN('28FEB2009','30APR2008')
= 00.10.00 (both dates = month end)
2.
If the later
date represents the ending date of its month, YMD_BETWEEN counts full months working
backwards from that ending date, and the number of days returned is the
number of days remaining in the month represented by the earlier date.
Example: YMD_BETWEEN('27FEB2008','31AUG2009')
= 01.06.02
This represents the six full months
of Mar 1 through Aug 31, plus the last 2 days of February 2008 (28th
and 29th). In the example, remember that 27FEB2008 is interpreted as
representing the end of that date, so there are only 2 days remaining in
the month of February 2008.
Example: YMD_BETWEEN('31AUG2009', '27MAR2008')
= 01.05.04
This represents the five full months
of Apr through Aug, plus the last 4 days of March (28th through 31st).
3.
If the earlier
date represents the ending date of its month, YMD_BETWEEN counts full months working
forwards from that ending date, and the number of days returned is the
day number of the later date.
Example: YMD_BETWEEN('31AUG2007', '24MAR2008')
= 00.06.24
This represents the six full months
of Sep through Feb, plus 24 days in March.
4.
When both dates
have the same day number (e.g. August 25th and December 25th),
the number of days returned is 0.
Example: YMD_BETWEEN('25AUG2007', '25DEC2008')
= 01.04.00
5.
When the day
number of the later date is greater than the day number of the earlier
date, full months are counted starting with the day number of the earlier
date, and the number of days is the result of subtracting the day number of the
earlier day from the day number of the later date.
Example: YMD_BETWEEN('15AUG2007', '27DEC2008')
= 01.04.12
In this example, the day number of
the later date (27th) is greater than the day number of the earlier
date (15th). Therefore, Rule 5 counts Sep 15 as 1 month, Oct 15 as
2 months, Nov 15 as 3 months, and Dec 15 as 4 months, then counts 12 days (Dec
16th through 27th).
6.
The only other
possibility is that the day number of the earlier date is greater than the day
number of the later date. When that is the case, full months are counted
starting with the day number of the earlier date, and the number of days is (A)
the day number of the later date plus (B) the number of remaining days
in the month immediately preceeding the month of the later date.
Example: YMD_BETWEEN('27AUG2007', '20DEC2008')
= 01.03.23
Rule 6 counts Sep 27 as 1 month, Oct
27 as 2 months, and Nov 27 as 3 months. There are 3 days left over at the end
of November (28th through 30th), plus 20 days at the beginning of December for
a total of 23 days.
Note: The decision to use this
method for Rule 6 was a conscious one. An alternative, which was explored,
would be to calculate the months as the number of full calendar months
in between the two dates, then calculate the days as the number of stub days
from each of the two dates. The results of this alternate method would be
slightly different from the selected method under certain conditions.
Example: 04Jun2010 to 02Aug2010.
Method 1 (in use): Begin with
04Jun2010, and count forward to 04Jul2010 as 1 month. There are 27 days
remaining in July (5th through 31st) plus 2 days in
August, for a total of 29 days.
Method 2 (considered): Count the
intervening month of July 2010 as 1 full month. There are 26 days remaining in
June (5th through 30th) plus 2 days in August for a total
of 28 days.
Method 1 was chosen for two
reasons. First, by counting months starting with the earlier date, it is
consistent with Rule 5. Second, Method 2 is inappropriate for Rule 5, because
it could result in silly results such as zero months plus 54 days for Mar05
through Apr28.
Alternate
3rd argument:
Including
the number 1 as the 3rd argument will calculate differences from the
beginning of the earlier date to the end of the later date. All
rules are adjusted accordingly, as shown in the examples below.
Example: YMD_BETWEEN('29FEB2008','31AUG2009') = 01.06.00
Example: YMD_BETWEEN('01MAR2008','31AUG2009',1) = 01.06.00
Example: YMD_BETWEEN('29FEB2008','31AUG2009',1) = 01.06.01
Example: YMD_BETWEEN('01MAY2008','28FEB2009',1) = 00.10.00
Example: YMD_BETWEEN('30APR2008','28FEB2009',1) = 00.10.01
Example: YMD_BETWEEN('27FEB2008','31AUG2009',1) = 01.06.03
Example: YMD_BETWEEN('28FEB2008','31AUG2009',1) = 01.06.02
Example: YMD_BETWEEN('27MAR2008','31AUG2009',1) = 01.05.05
Example: YMD_BETWEEN('27MAR2008','01SEP2009',1) = 01.05.06
Custom Function: MOS_BETWEEN
The
MOS_BETWEEN function is intended as an improvement to Oracle's MONTHS_BETWEEN
function. Building on the YMD_BETWEEN custom function described above,
MOS_BETWEEN will calculate the number of months and portions of a month based
on the value of YMD_BETWEEN. Just like MONTHS_BETWEEN, MOS_BETWEEN will return
a negative or positive number, based on the position of the earlier and later
dates in the list of arguments. However, MOS_BETWEEN corrects some
shortcomings present in Oracle's MONTHS_BETWEEN function.
Consider
these two calls to Oracle's own Months_Between function:
Select
months_between('28FEB2009','31JAN2009') from dual;
Value
returned: 1
This
makes sense. There is exactly one month between the end of January and the end
of February.
Now
let's ADD one day to the range.
Select
months_between('28FEB2009','30JAN2009') from dual;
Instead
of starting with January 31, we'll start with January 30. Logically, the
result should add one day, or 1/31st of a month, so we should see
something like 1.03225.
Value
actually returned: .9354
This,
of course, makes no sense whatsoever, unless you are looking to win a bar bet
by proving that 30 is greater than 31.
Using
the Mos_Between function produces reasonable results:
Select
mos_between('28FEB2009','31JAN2009') from dual;
Result:
1
Select
mos_between('28FEB2009','30JAN2009') from dual;
Result:
1.032258065
Function
Code
create or replace function YMD_BETWEEN
(date1_in IN date, date2_in IN date, type_in in
number default 0 )
return varchar
IS cvalue varchar(20);
cursor tb is
select *
from
(select YY || '.' ||
MM || '.' ||
DD as YMD
from
(select substr(to_char(100 + trunc(MOS/12,0)),2,2)
as YY,
substr(to_char(100 + mod(MOS,12)),2,2) as
MM,
substr(to_char(100 + DD),2,2) as DD
from
(select Two.*,
End_Mo - Begin_Mo
- case when to_char(Begin_Date+1,'DD') = '01'
and to_char(End_Date+1,'DD') = '01'
THEN 0
when to_char(End_Date+1,'DD') = '01'
THEN 0
when to_char(Begin_Date+1,'DD') = '01'
THEN 1
when to_char(End_Date,'DD') =
to_char(Begin_Date,'DD') then 0
when to_char(End_Date,'DD') <
to_char(Begin_Date,'DD') then 1
else 0 end as MOS,
case when to_char(Begin_Date+1,'DD') = '01'
and to_char(End_Date+1,'DD') = '01'
THEN 0
when to_char(End_Date+1,'DD') = '01'
THEN M1Days
when to_char(Begin_Date+1,'DD') = '01'
then to_number(to_char(End_Date,'DD'))
when to_char(End_Date,'DD') = to_char(Begin_Date,'DD')
then 0
when to_char(End_Date,'DD') >
to_char(Begin_Date,'DD')
then to_char(End_Date,'DD') -
to_char(Begin_Date,'DD')
else to_char(add_months('01' ||
to_char(End_Date,'MONYYYY'),0)-1,'DD')
- to_char(Begin_Date,'DD') +
to_char(End_Date,'DD')
end as DD
from
(select One.*,
to_char(Begin_Date,'YYYY')*12 +
to_char(Begin_Date,'MM') as Begin_Mo,
to_char(End_Date,'YYYY')*12 +
to_char(End_Date,'MM') as End_Mo,
to_char(add_months('01' ||
to_char(Begin_Date,'MONYYYY'),1)-1,'DD')
- to_char(Begin_Date,'DD') as M1Days
from
--One
(select Begin_Date - case when type_in = 1 then 1
else 0 end as Begin_Date,
End_Date
from
(select case when date1_in < date2_in then
date1_in else date2_in end as Begin_Date,
case when date1_in < date2_in then
date2_in else date1_in end as End_Date
from dual)) One) Two)));
begin
open tb;
fetch tb into cvalue;
close tb;
return cvalue;
END;
create or replace
function MOS_BETWEEN
(date1_in IN date, date2_in IN date, type_in in
number default 0 )
return number
IS cvalue number;
cursor tb is
select (to_number(substr(YMD,1,2))*12
+ to_number(substr(YMD,4,2))
+ to_number(substr(YMD,7,2))/31)
* case when date1_in < date2_in then -1 else
1 end AS MOS
from
(select YMD_BETWEEN(date1_in, date2_in, type_in) as
YMD from dual);
begin
open tb;
fetch tb into cvalue;
close tb;
return cvalue;
END;
