| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Months_Between flaw?
A copy of this was sent to tabates_at_my-dejanews.com
(if that email address didn't require changing)
On Sat, 03 Apr 1999 00:34:15 GMT, you wrote:
>I believe that there is a flaw with the Months_Between function.
>There are times where I need to select transactions that have occured within
>the past month (calendar). I was using something akin to:
>select sum(amount) from transactions
>where trunc(months_between(today, processed))=0;
>
>During testing I found that it would include invalid data.
>The error occurs when today = '28-Feb-99'. Transactions on '1/31/99' will not
>be included, however, events on 1/29 and 1/30 will.
>I have since avoided this issue by the using the following:
>select sum(amount) from transactions
>where to_char(processed, 'MM/yyyy') = to_char(today, 'MM/yyyy');
>
>Comments?
>
Its the way its supposed to work:
<quote sql ref manual>
Returns number of months between dates d1 and d2. If d1
is later than d2, result is positive; if earlier, negative. If d1
and d2 are either the same days of the month or both last
days of months, the result is always an integer; otherwise
Oracle7 calculates the fractional portion of the result based
on a 31–day month and considers the difference in time
components of d1 and d2.
</quote>
Since 31-jan-99 and 28-feb-99 are both the last days of the month, it returns an integer -- there is 1 month between the two.
The real problem is: define what a calendar month is. I mean, given that today is April 2, 1999 -- what falls into the last calendar month? is it everything back to March 2'cnd? Is it 30 days? 28 days? If you define what a calendar month is, we can define a date function to get that. It looks like what you really wanted was everything in this month -- in which case your:
where to_char(processed, 'MM/yyyy') = to_char(today, 'MM/yyyy');
will work fine.
SQL> select months_between( '31-jan-99', '28-feb-99' ) from dual;
MONTHS_BETWEEN('31-JAN-99','28-FEB-99')
-1
SQL> c/31/30
1* select months_between( '30-jan-99', '28-feb-99' ) from dual
SQL> /
MONTHS_BETWEEN('30-JAN-99','28-FEB-99')
-.93548387
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |