Q:

SQL available instruments between selected dates

I have an instrument booking system and I want to find instruments that are available.
I want to select all instrument information when the Instrument is not in the Rental table between the selected dates.
I am using the following code but it is not returning any results (there should be 5 available)
public List<Instrument> availableInstruments(DateTime startDate, DateTime returnDate)
    {
        //string startDate1 = startDate.ToString("yyyy-MM-dd HH:mm:ss.fff");
        //string returnDate1 = returnDate.ToString("yyyy-MM-dd HH:mm:ss.fff");
       List<Instrument> availableInstruments = new List<Instrument>();
       db.Cmd.Parameters.Add(new SqlParameter("@startDate1", startDate));
       db.Cmd.Parameters.Add(new SqlParameter("@returnDate1", returnDate));
       db.Cmd.CommandText = "SELECT * FROM InstrumentTBL WHERE InstrumentID NOT IN (SELECT InstrumentTBL.InstrumentID FROM InstrumentTBL IT JOIN RentalTBL DT ON IT.InstrumentID = DT.InstrumentID Where(DT.DateOut <= @startDate1  AND DT.ReturnDate >= @returnDate1) OR (DT.DateOut < @returnDate1 AND DT.ReturnDate >= @returnDate1) OR (@startDate1 <= DT.DateOut AND @returnDate1 >= DT.DateOut))";
       db.Rdr = db.Cmd.ExecuteReader();
       while (db.Rdr.Read())
       {
           availableInstruments.Add(getInstrumentFromReader(db.Rdr)); // store into list
       }
        db.Rdr.Close();
        return availableInstruments;
    }

I am using 2 tables an instrument table and a rental table, the dateout and returndate are in the rental table.
I got the code from another thread and I tried to adopt it but with little success.
Please any help at all is welcomed.

A:

Try this in your select
SELECT * FROM InstrumentTBL 
WHERE InstrumentID NOT 
IN (SELECT DT.InstrumentID 
    FROM RentalTBL DT 
    Where(DT.DateOut >= @startDate1  AND DT.ReturnDate <= @returnDate1)) 

