SQL to display all days between two dates

Question:  How do I write a SQL to display the number of months between two dates?
 
Answer:  Here is a simple way to display the dates between two dates:

BEWARE:  Using the trunc function will invalidate the index on order_date, and you may want to create a temporary function-based index on trunc(order_date)

select
   order_nbr
from
  sales_order
where
   trunc(ORDER_DATE) between
     to_date('03-01-2016','MM-DD-YYYY')
   and
     to_date('03-11-2016','MM-DD-YYYY');

Here is a way to display the dates between two dates in PL/SQL:

set serveroutput on size 1000000
declare
start_date date := to_date('01-JAN-2016','DD-MON-YYYY');
end_date   date := to_date('05-JAN-2016','DD-MON-YYYY');
Begin
loop
   start_date := start_date +1;
   if start_date < end_date then
      dbms_output.put_line(start_date);
   else
      exit;
   end if;
end loop;
exit;
/

Here is yet another way to get the dates between two dates:

select
   to_date('03-01-2015','dd-mon-yyyy') + rownum -1
from
   all_objects
where
   rownum <=
   to_date('01-01-2016','dd-mon-yyyy')-to_date('03-01-2015','dd-mon-yyyy')+1;

And another way to get dates between two dates:

select
   to_date('04-01-2016','dd-mm-yyyy') + lvl
from
   (select level - 1 lvl
    from
      dual
    connect by
   level <= (to_date('10-01-2015','dd-mm-yyyy') - to_date('04-01-2016','dd-mm-yyyy'))+ 1);

And yet another way to get the dates between two dates:

select * from
   (select
      rno||'-'||to_char(:p_start_date,'MON-YYYY') date_val
    from(
      select
         rownum
         from
         (
           select 1 from dual
           group by cube(0,0,0,0,0,0)
         )
      where
   rownum < to_char(last_day(:p_start_Date),'DD')))
where date_val between :p_start_date and :p_end_date;

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.