Re: Months_Between flaw?

On Sat, 03 Apr 1999 00:34:15 GMT, in
comp.databases.oracle.server you wrote:

>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.
<snip>
>Comments?

You have to read the docco for months_between very carefully. The rules are different if your dates are end of the month (or is it first of the month) as opposed to somewhere during the month.

It sounds like you want to check for both dates being in the same calendar month. Your solution, converting the dates to mm/yyyy strings and comparing those strings, is one way to do that. Another possibilty might be to try something like this:

where trunc(one_date,'MM') = trunc(other_date,'MM')

I havn't tried the above, have no idea whether it's better, more efficient, and so forth, but I think it would also work.

regards,

Jonathan Received on Sat Apr 03 1999 - 17:25:46 CST