Home » SQL & PL/SQL » SQL & PL/SQL » get list of months between from_date and to_date
get list of months between from_date and to_date [message #212413] Fri, 05 January 2007 04:25 Go to next message
laksha
Messages: 42
Registered: June 2006
Member
How do i get a list of months (MON-YEAR) between two date parameters 'from_date' and 'to_date'.

eg if FROM_DATE is 12-JUL-2006 and TO_DATE is 17-FEB-2007
then i have to get list of each months between them like
JUL-2006
AUG-2006
SEP-2006
.
.
.
JAN-2007
FEB-2007

Thanks in advance.
Re: get list of months between from_date and to_date [message #212417 is a reply to message #212413] Fri, 05 January 2007 04:36 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
If you are using Oracle 9i and above then you can probably try this (Not tested code)

SQL> variable startdate varchar2(20);
SQL> variable enddate varchar2(20);
SQL> exec :startdate := '12-Jul-2006';
SQL> exec :enddate := '17-Feb-2007';
SQL> Select to_char(add_months(to_date(:startdate,'dd-Mon-yyyy'),r),'Mon-yyyy') lis_mon
from (select rownum - 1 r from dual 
connect by level <= months_between(to_date(:startdate,'dd-Mon-yyyy'),to_date(:enddate,'dd-Mon-yyyy')) )
/




Re: get list of months between from_date and to_date [message #212426 is a reply to message #212413] Fri, 05 January 2007 04:59 Go to previous messageGo to next message
anilsinare
Messages: 22
Registered: December 2005
Location: ipswich, uk
Junior Member
Hi,
You can use following query. Hope this helps you.

SELECT
TO_CHAR(add_months(trunc(to_date('12-JUL-2006'), 'MONTH'),sr_no-1),'mon-yyyy') Months FROM
(select
rownum SR_NO from user_objects)
where add_months(trunc(to_date('12-JUL-2006'), 'MONTH'),sr_no-1) between
trunc(to_date('12-JUL-2006'), 'MONTH') and trunc(to_date('12-FEB-2007'), 'MONTH');

Regards,
Anil
Re: get list of months between from_date and to_date [message #212427 is a reply to message #212417] Fri, 05 January 2007 05:01 Go to previous message
laksha
Messages: 42
Registered: June 2006
Member
Thanks. Got the idea.
Quote:

Select to_char(add_months(to_date(:startdate,'dd-Mon-yyyy'),r),'MON-YYYY') lis_mon
from (select rownum -1 r from dual
connect by level <= months_between(to_date(:enddate,'dd-Mon-yyyy'),to_date(:startdate,'dd-Mon-yyyy'))+1 )


Previous Topic: Primary Key Creation
Next Topic: define row size of a table
Goto Forum:
  


Current Time: Sat Dec 10 16:55:09 CST 2016

Total time taken to generate the page: 0.06768 seconds