Tuesday, December 3, 2013

YMD_BETWEEN and MOS_BETWEEN

Not a Meta Tag, but required anyway

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;

2 comments:

  1. Nice information. I was searching for the same. It helped me alot and saved my time. Thanks alot. obiee training in hyderabad

    ReplyDelete
    Replies
    1. Thanks, Mehak. If you don't mind, please return here in a month or so and let me know if this worked for you. Especially let me konw if there is something that looks odd or just flat doesn't work as advertised.

      Delete