Display months of a deternimado range

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Thu, 08 Sep 2005 11:09 am

Hello all! I have a problem yet unsolved. There is a query where I can generate the months/years of a range specified in WHERE clause?
For example. I would like to display the records in format (mm/yyyy): 01/02/03/2005 2005 2005 2005 2005/04/05 06/2005 Where can pass on WHERE this interval 01/2005 and 06/2005.

Thanks guys!
brunomachado
Location: Fortaleza - CE

Poston Thu, 08 Sep 2005 11:48 am

Take a look at this:
Code: Select all
select
  add_months(trunc( DATA1  ,'mm'),rownum-1)
from user_tables
where rownum <= months_between ( DATA 2)
                               , DATA 1
                               ) + 1
Example: (from 10/2005 to 12/2005)
Code: Select all
SQL> select
  2    add_months(trunc( TO_DATE('10/2005','MM/YYYY')  ,'mm'),rownum-1)
  3  from user_tables
  4  where rownum <= months_between ( to_date('12/2005','mm/yyyy')
  5                                 , to_date('10/2005','mm/yyyy')
  6                                 ) + 1
  7  /

ADD_MONTH
---------
01-OCT-05
01-NOV-05
01-DEC-05

3 rows selected.
Example: (09/2004 to 01/2006)
Code: Select all
SQL> select
  2    add_months(trunc( TO_DATE('09/2004','MM/YYYY')  ,'mm'),rownum-1)
  3  from user_tables
  4  where rownum <= months_between ( to_date('01/2006','mm/yyyy')
  5                                 , to_date('09/2004','mm/yyyy')
  6                                 ) + 1
  7  /

ADD_MONTH
---------
01-SEP-04
01-OCT-04
01-NOV-04
01-DEC-04
01-JAN-05
01-FEB-05
01-MAR-05
01-APR-05
01-MAY-05
01-JUN-05
01-JUL-05
01-AUG-05
01-SEP-05
01-OCT-05
01-NOV-05
01-DEC-05
01-JAN-06

17 rows selected.
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered



Return to SQL

Who is online

Users browsing this forum: No registered users and 4 guests