Get dates between two months Can I know why am I getting weird output in my SQL statement whereby my table has two dates of the month May but it only appears for one month, the same things I test for dates range it is working.

    select distinct To_Char (attendance_date,'dd/MM/yyyy') 
    from DIT_2010MAR_CIT4114A_FYP1_NO
    where attendance_Date between To_Date ('05', 'MM') and 
    To_Date ('05', 'MM');
Actually your query doesn't return anything in my test :(. The reason is the attendance_date doesn't fall in between the condition that you have specified(To_date('05','MM') and 
 To_date('05','MM') is returning the same value). 

You can change your code as 
  ...where attendance_date between To_date('05','MM') and To_date('06','MM'); to select all the dates of the May month.

SQL&gt; desc atten;
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ATTENDANCE_DATE                    DATE

SQL&gt; select * from atten;

ATTENDANC
---------
05-MAY-16
03-JUN-16
03-JUL-16
03-MAY-16

SQL&gt; select distinct To_Char (attendance_date,'dd/MM/yyyy') from atten where attendance_Date between To_Date ('05', 'MM') and To_Date ('05', 'MM');

no rows selected


You can try the following to get the result.

SQL&gt; select to_char(attendance_date, 'dd/mm/yyyy') from atten
where extract(month from attendance_date) = 5;  2  

TO_CHAR(AT
----------
05/05/2016
03/05/2016


OR If you wish to display attendance date of months between 5 and 6(As you question's title suggests).

select to_char(attendance_date, 'dd/mm/yyyy') from atten
where extract(month from attendance_date) = 5 OR extract(month from attendance_date)=6;
TO_CHAR(AT
----------
05/05/2016
03/06/2016
03/05/2016