Home » SQL & PL/SQL » SQL & PL/SQL » months between
months between [message #215025] Fri, 19 January 2007 00:28 Go to next message
sujeethbala
Messages: 25
Registered: October 2006
Location: INDIA
Junior Member
hi

i want to get the number of months between 2 dates. result should be inclusive of both the months.
e.g
if date1=18-jan-2007 and date2=10-feb-2007
the result i want is 2 (months)
can anyone tell me how i can get this result.

thanks
Re: months between [message #215031 is a reply to message #215025] Fri, 19 January 2007 01:09 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
select (months_between(last_day(to_date('18-02-2007', 'dd-mm-yyyy')), last_day(to_date('10-01-2007', 'dd-mm-yyyy')))) + 1  
from dual

First make sure you get a whole number for the months_between. This is done by taking the last_day of both months. Then add 1.
Re: months between [message #215037 is a reply to message #215031] Fri, 19 January 2007 01:22 Go to previous messageGo to next message
sujeethbala
Messages: 25
Registered: October 2006
Location: INDIA
Junior Member
thanks
it works for me
Re: months between [message #215206 is a reply to message #215025] Fri, 19 January 2007 13:06 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
The nice thing about oracle is that you can do something 20 different ways. Another way using trunc is

select (months_between(trunc(to_date('18-02-2007', 'dd-mm-yyyy'),'month'), trunc(to_date('10-01-2007', 'dd-mm-yyyy'),'month'))) + 1 tot_months
from dual;
Previous Topic: Retrieving the 2 most recent records from a table by date
Next Topic: How to Insert Data into a CLOB Column of 86000 characters long
Goto Forum:
  


Current Time: Thu Dec 08 04:13:04 CST 2016

Total time taken to generate the page: 0.09280 seconds