### 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 :-)