Count number of months in Date range

[jasonhuibers]

Date1: Oct 12-2012
Date2: Dec 7-2012

Using a query how can I determine the number of months in a date rane using Date1 and Date2 above?

Ie:
3 (as in 3 months - Oct, Nov and Dec)

 

[jasonhuibers]

Not sure if this would do the trick?

SELECT round(MONTHS_BETWEEN (DATE '2012-12-07', DATE '2012-10-12') + 1) FROM dual;

 

[SantaMufasa]

My motto with anything Oracle (or other technology) is:

One test is worth 1000 expert opinions.

So, did your code work for you? (I believe it did, right?)

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[jasonhuibers]

Actually the SELECT I posted works if it is more than 1 month but does not work for the following:

SELECT round(MONTHS_BETWEEN (DATE '2012-12-31', DATE '2013-01-07') + 1) FROM dual;

This should be actually two months:
Dec
Jan

For the code I posted for more than one month it works fine:
SELECT round(MONTHS_BETWEEN (DATE '2012-12-07', DATE '2012-10-12') + 1) FROM dual;

3months
Dec
Nov
Oct

Anyone have any ideas?

 

[SantaMufasa]

Jason,

I don't know what you are expecting for results, but your logic between the two SQL statements is an apples-to-oranges comparison: In the first SQL statement, you are comparing an earlier date to a later date, for which (in your case) the result is -.22580645 (of a month). In the second SQL statement, you are comparing a later date to an earlier date, for which (in your case) the result is 1.83870968 (months).

In both cases, Oracle's SQL does precisely what you are telling it to do.

Please explain what you are hoping it will do differently.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.