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 numberisbegin 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 ,52012-10-01 00:00:00 2012-10-16 00:00:00 ,4839 ,48392012-10-01 00:00:00 2012-10-16 12:00:00 ,5 ,52012-07-10 00:00:00 2012-08-10 00:00:00 1 12012-08-10 00:00:00 2012-09-10 00:00:00 1 1,00972012-09-10 00:00:00 2012-10-10 00:00:00 1 ,99032011-07-10 00:00:00 2012-08-10 00:00:00 13 132002-08-10 00:00:00 2012-09-10 00:00:00 121 121,00972012-07-10 00:00:00 2012-08-10 12:00:00 1 1,01612012-08-10 00:00:00 2012-08-10 12:00:00 0 ,01612012-09-30 00:00:00 2012-10-31 00:00:00 1 1,00112012-09-30 00:00:00 2012-10-31 12:00:00 1 1,01722012-02-29 00:00:00 2012-03-31 00:00:00 1 1,00222011-02-28 00:00:00 2011-03-31 00:00:00 1 1,003514 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 ,5September 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 ,4839October 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 ,5October 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 1July 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,0097August 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 ,9903September 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 13This one just for testing both functions work across years.2002-08-10 00:00:00 2012-09-10 00:00:00 121 121,0097Standard 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,0161Here 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 ,0161Similar 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,0011Here 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,0172Here 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,0022Here 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,0035Here 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 :-)