How to get a list of months between 2 given dates using a query?

oracle date-arithmetic
Nitish picture Nitish · Aug 3, 2011 · Viewed 35.6k times · Source

I have 2 dates, say 28-Mar-2011 and 29-Jun-2011. I need an sql query that will display the months between these 2 dates including the months containing the dates, ie. June, May, April and March.

Answer

Justin Cave picture Justin Cave · Aug 3, 2011

Something like this

SQL> ed
Wrote file afiedt.buf

    select to_char( add_months( start_date, level-1 ), 'fmMonth' )
      from (select date '2011-03-30' start_date,
                   date '2011-06-29' end_date
              from dual)
     connect by level <= months_between(
                           trunc(end_date,'MM'),
                           trunc(start_date,'MM') )
  *                      + 1
SQL> /

TO_CHAR(ADD_MONTHS(START_DATE,LEVEL-
------------------------------------
March
April
May
June

should work.

Related questions

Calculate difference between 2 date / times in Oracle SQL

I have a table as follows: Filename - varchar Creation Date - Date format dd/mm/yyyy hh24:mi:ss Oldest cdr date - Date format dd/mm/yyyy hh24:mi:ss How can I calcuate the difference in hours …

Read More
Oracle DateTime in Where Clause?

I have sql something like this: SELECT EMP_NAME, DEPT FROM EMPLOYEE WHERE TIME_CREATED >= TO_DATE('26/JAN/2011','dd/mon/yyyy') -> This returns 10 rows and TIME_CREATED = '26-JAN-2011' Now when i do this …

Read More
Oracle date "Between" Query

I am using oracle database. i want to execute one query to check the data between two dates. NAME START_DATE ------------- ------------- Small Widget 15-JAN-10 04.25.32.000000 PM Product 1 17-JAN-10 04.31.32.000000 PM select * from <TABLENAME> where start_date BETWEEN '15…

Read More