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:
And so I tested my alternative months between function:
(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:
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.
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.
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.
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.
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.
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.
This one just for testing both functions work across years.
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.
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!
Similar case:
Standard function notices both dates are same day of month and states this is zero months exactly.
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.
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.
Here the standard notices both dates are end-of-month and states this is one month exactly.
Here the standard notices both dates are end-of-month and states this is one month exactly.
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:
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 :-)
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 :-)
Comments
Post a Comment