user_id, start_date, end_date , total_days

user_id as 1, start_date as 05/09/2012 , end_date as 10/09/2012 and total_days as 5.

SELECT user_id FROM table
  WHERE
    (start_date >= '2012-09-07' AND start_date <= '2012-09-08')
  OR
    (end_date >= '2012-09-07' AND end_date <= '2012-09-08')
  OR
    (start_date <= '2012-09-07' AND end_date >= '2012-09-08');

select * from Leaves
where STR_TO_DATE(start_date,'%M/%d/%y') >= STR_TO_DATE('07/09/2012','%M/%d/%y') and
      STR_TO_DATE(endt_date,'%M/%d/%y') <= STR_TO_DATE('08/09/2012','%M/%d/%y')

User.leaves.find(:all, :conditions => ["(start_date <= ? AND end_date >= ?) OR (start_date <= ? AND end_date >= ?)", '2012-09-07', '2012-09-07', '2012-09-08', '2012-09-08'])