Thursday, October 4, 2012

Fiddling with MONTHS_BETWEEN

A few days ago I answered a question on OTN SQL and PL/SQL forum that prompted me to fiddle around with MONTHS_BETWEEN. I did discover something new that I wasn't aware of while developing an alternative MONTHS_BETWEEN implementation.

The forum poster had a requirement where employee allowance was to be calculated based on a from and to date. A fixed monthly allowance was given which then could be multiplied to get the total allowance, but if 20 days had been worked in April allowance was to be 20/30 of the monthly figure, while 20 days in May would give 20/31 of the monthly figure.



The immediate response that came to mind was MONTHS_BETWEEN and just as quickly rejected because it always calculates with a 31 day month no matter if we are in February, April or May. So I did an alternative function and answered the forum poster, and he could use it so I was happy :-)

But the answer I gave used extract of day in the calculations and so it could only do "whole days" and not use the time part of the data to be able to calculate fractions of days. So I fiddled around and created another version of my function:

create or replace function alt_months_between (
   p_todate    date
 , p_fromdate  date
)
   return number
is
begin
   return /* fraction of months from p_fromdate until the end of the month */
          ( (last_day(trunc(p_fromdate)) + 1 - p_fromdate)
            / extract(day from last_day(p_fromdate)) )
          /* whole months between the dates (-1 for dates in the same month) */
        + (months_between(trunc(p_todate,'MM'),trunc(p_fromdate,'MM'))-1)
          /* fraction of months from start of month until p_todate */
        + ( (p_todate - trunc(p_todate,'MM'))
            / extract(day from last_day(p_todate)) );
end alt_months_between;
/

And so I tested my alternative months between function:

SQL> with test as (
  2     select to_date('2012-09-01 00:00:00','YYYY-MM-DD HH24:MI:SS') fromdate
  3          , to_date('2012-09-16 00:00:00','YYYY-MM-DD HH24:MI:SS') todate
  4       from dual
  5      union all
  6     select to_date('2012-10-01 00:00:00','YYYY-MM-DD HH24:MI:SS') fromdate
  7          , to_date('2012-10-16 00:00:00','YYYY-MM-DD HH24:MI:SS') todate
  8       from dual
  9      union all
 10     select to_date('2012-10-01 00:00:00','YYYY-MM-DD HH24:MI:SS') fromdate
 11          , to_date('2012-10-16 12:00:00','YYYY-MM-DD HH24:MI:SS') todate
 12       from dual
 13      union all
 14     select to_date('2012-07-10 00:00:00','YYYY-MM-DD HH24:MI:SS') fromdate
 15          , to_date('2012-08-10 00:00:00','YYYY-MM-DD HH24:MI:SS') todate
 16       from dual
 17      union all
 18     select to_date('2012-08-10 00:00:00','YYYY-MM-DD HH24:MI:SS') fromdate
 19          , to_date('2012-09-10 00:00:00','YYYY-MM-DD HH24:MI:SS') todate
 20       from dual
 21      union all
 22     select to_date('2012-09-10 00:00:00','YYYY-MM-DD HH24:MI:SS') fromdate
 23          , to_date('2012-10-10 00:00:00','YYYY-MM-DD HH24:MI:SS') todate
 24       from dual
 25      union all
 26     select to_date('2011-07-10 00:00:00','YYYY-MM-DD HH24:MI:SS') fromdate
 27          , to_date('2012-08-10 00:00:00','YYYY-MM-DD HH24:MI:SS') todate
 28       from dual
 29      union all
 30     select to_date('2002-08-10 00:00:00','YYYY-MM-DD HH24:MI:SS') fromdate
 31          , to_date('2012-09-10 00:00:00','YYYY-MM-DD HH24:MI:SS') todate
 32       from dual
 33      union all
 34     select to_date('2012-07-10 00:00:00','YYYY-MM-DD HH24:MI:SS') fromdate
 35          , to_date('2012-08-10 12:00:00','YYYY-MM-DD HH24:MI:SS') todate
 36       from dual
 37      union all
 38     select to_date('2012-08-10 00:00:00','YYYY-MM-DD HH24:MI:SS') fromdate
 39          , to_date('2012-08-10 12:00:00','YYYY-MM-DD HH24:MI:SS') todate
 40       from dual
 41      union all
 42     select to_date('2012-09-30 00:00:00','YYYY-MM-DD HH24:MI:SS') fromdate
 43          , to_date('2012-10-31 00:00:00','YYYY-MM-DD HH24:MI:SS') todate
 44       from dual
 45      union all
 46     select to_date('2012-09-30 00:00:00','YYYY-MM-DD HH24:MI:SS') fromdate
 47          , to_date('2012-10-31 12:00:00','YYYY-MM-DD HH24:MI:SS') todate
 48       from dual
 49      union all
 50     select to_date('2012-02-29 00:00:00','YYYY-MM-DD HH24:MI:SS') fromdate
 51          , to_date('2012-03-31 00:00:00','YYYY-MM-DD HH24:MI:SS') todate
 52       from dual
 53      union all
 54     select to_date('2011-02-28 00:00:00','YYYY-MM-DD HH24:MI:SS') fromdate
 55          , to_date('2011-03-31 00:00:00','YYYY-MM-DD HH24:MI:SS') todate
 56       from dual
 57  )
 58  select to_char(fromdate,'YYYY-MM-DD HH24:MI:SS') fromdate
 59       , to_char(todate,'YYYY-MM-DD HH24:MI:SS') todate
 60       , round(months_between(todate,fromdate),4) std_months
 61       , round(alt_months_between(todate,fromdate),4) alt_months
 62    from test
 63  /

FROMDATE            TODATE              STD_MONTHS ALT_MONTHS
------------------- ------------------- ---------- ----------
2012-09-01 00:00:00 2012-09-16 00:00:00      ,4839         ,5
2012-10-01 00:00:00 2012-10-16 00:00:00      ,4839      ,4839
2012-10-01 00:00:00 2012-10-16 12:00:00         ,5         ,5
2012-07-10 00:00:00 2012-08-10 00:00:00          1          1
2012-08-10 00:00:00 2012-09-10 00:00:00          1     1,0097
2012-09-10 00:00:00 2012-10-10 00:00:00          1      ,9903
2011-07-10 00:00:00 2012-08-10 00:00:00         13         13
2002-08-10 00:00:00 2012-09-10 00:00:00        121   121,0097
2012-07-10 00:00:00 2012-08-10 12:00:00          1     1,0161
2012-08-10 00:00:00 2012-08-10 12:00:00          0      ,0161
2012-09-30 00:00:00 2012-10-31 00:00:00          1     1,0011
2012-09-30 00:00:00 2012-10-31 12:00:00          1     1,0172
2012-02-29 00:00:00 2012-03-31 00:00:00          1     1,0022
2011-02-28 00:00:00 2011-03-31 00:00:00          1     1,0035

14 rows selected.

(For any americans out there: I am in Denmark where the decimal separator is a comma ;-)

Let's walk through the results one at a time:


2012-09-01 00:00:00 2012-09-16 00:00:00      ,4839         ,5

September has 30 days and SEP-01 midnight until SEP-16 midnight is 15 days.
Standard function uses 31 days and states it is a little less than half a month.
Alternative function uses 30 days and states half a month exactly.

2012-10-01 00:00:00 2012-10-16 00:00:00      ,4839      ,4839


October has 31 days and OCT-01 midnight until OCT-16 midnight is 15 days.
Standard function uses 31 days and states it is a little less than half a month.
Alternative function also uses 31 days and states the same.


2012-10-01 00:00:00 2012-10-16 12:00:00         ,5         ,5


October has 31 days and OCT-01 midnight until OCT-16 noon is 15½ days.
Standard function uses 31 days and states half a month exactly.
Alternative function also uses 31 days and states the same.
In this case both functions use the time part to calculate 15½ days.


2012-07-10 00:00:00 2012-08-10 00:00:00          1          1


July has 31 days - from JUL-10 midnight until the end of the month is 22 days.
August also has 31 days - from start of the month until AUG-10 midnight is 9 days.
Standard function notices both dates are same day of month and states this is one month exactly.
Alternative function calculates 22 / 31 + 9 / 31, which is also one month exactly.


2012-08-10 00:00:00 2012-09-10 00:00:00          1     1,0097


August has 31 days - from AUG-10 midnight until the end of the month is 22 days.
September only has 30 days - from start of the month until SEP-10 midnight is 9 days.
Standard function notices both dates are same day of month and states this is one month exactly.
Alternative function calculates 22 / 31 + 9 / 30, which is a little more than one!
This may feel "wrong" but is correct for the desired allowance calculation where these 31 days should give 22/31 parts of August allowance + 9/30 parts of September allowance.


2012-09-10 00:00:00 2012-10-10 00:00:00          1      ,9903


September has 30 days - from SEP-10 midnight until the end of the month is 21 days.
October has 31 days - from start of the month until OCT-10 midnight is 9 days.

Standard function notices both dates are same day of month and states this is one month exactly.
Alternative function calculates 21 / 30 + 9 / 31, which is a little less than one!
Again this is correct that the 30 days should give 21/30 parts of September allowance + 9/31 parts of October allowance.



2011-07-10 00:00:00 2012-08-10 00:00:00         13         13

This one just for testing both functions work across years.

2002-08-10 00:00:00 2012-09-10 00:00:00        121   121,0097

Standard function notices both dates are same day of month and gets 120 (10 years times 12) + 1 = 121.
Alternative function calculates 22 / 31 + 119 + 9 / 30, which is a little more than 121!
Same reasoning as above - it also works across many months.

2012-07-10 00:00:00 2012-08-10 12:00:00          1     1,0161

Here is an interesting one:

Standard function notices both dates are same day of month and states this is one month exactly.
Alternative function calculates 22 / 31 + 9½ / 31, which is a little more than one!
In this case (as opposed to the third example) the standard function ignores the timepart - simply because the dates are the same day of the month!


2012-08-10 00:00:00 2012-08-10 12:00:00          0      ,0161

Similar case:
Standard function notices both dates are same day of month and states this is zero months exactly.
Alternative function calculates ½ / 31, which is a little more than zero.
2012-09-30 00:00:00 2012-10-31 00:00:00 1 1,0011

Here the standard notices both dates are end-of-month and states this is one month exactly.
Alternative function calculates 1 / 30 + 30 / 31, which is a little more than one.

2012-09-30 00:00:00 2012-10-31 12:00:00          1     1,0172


Here the standard notices both dates are end-of-month and states this is one month exactly.
Alternative function calculates 1 / 30 + 30½ / 31, which is a little more than one.
Again the standard function ignores time part because of special dates!


2012-02-29 00:00:00 2012-03-31 00:00:00          1     1,0022


Here the standard notices both dates are end-of-month and states this is one month exactly.
The alternative function calculates 1 / 29 + 30 / 31, which is a little more than one.


2011-02-28 00:00:00 2011-03-31 00:00:00          1     1,0035



Here the standard notices both dates are end-of-month and states this is one month exactly.
The alternative function calculates 1 / 28 + 30 / 31, which is a little more than one.
And as it is a non-leap year the 1 day in February gives 1/28 part of the allowance rather than 1/29 parts in leap years.



Lessons learned during this fiddling:

First lesson I knew already: What the calculations of "fractions of a month" should be, will be dependent on the circumstances, business rules, and other things. Find out the desired formula that fits the user requirements. If the standard MONTHS_BETWEEN does not fit, create your own alternative function.

Second lesson was new to me: The standard MONTHS_BETWEEN uses the time part of a DATE value differently depending on what day of the month the two parameters are! That was a surprise for me...

Of course it is documented - here is a quote from the docs:

MONTHS_BETWEEN returns number of months between dates date1 and date2. The month and the last day of the month are defined by the parameter NLS_CALENDAR. If date1 is later than date2, then the result is positive. If date1 is earlier than date2, then the result is negative. If date1 and date2 are either the same days of the month or both last days of months, then the result is always an integer. Otherwise Oracle Database calculates the fractional portion of the result based on a 31-day month and considers the difference in time components date1 and date2.

What I personally feel is that to give consistent results, Oracle could very well have chosen to ignore time component all the time. This implementation does not make much sense to me? At the least I think I will make sure, that whenever I use the standard MONTHS_BETWEEN I will probably always use TRUNC on the parameters to ensure consistent results no matter what day of month and time of day they are :-)

No comments:

Post a Comment