how to find bookings between dates in the following sql table

Actually, if you want bookings where the Start Date is before or inside your "window", and want those where the end date is inside or after your window, you will need:

Select * FROM booking_table
WHERE ArrivalDate <= [WindowEndDate]
AND DepartureDate >= [WindowStartDate]

You have to be more specific ... I mean do you want the search for arrival date only or arrival and departure

Example will probably help (excel sheet format)

If your Admin was choosing just one date, the select statement would be:

SELECT * FROM bookings
WHERE @date BETWEEN arrivaldate AND departuredate

However, because they want to find the bookings between a date range, you will have to loop through each date to get all results. Try using something like this (I had to set my date format, but you may not need to):

CREATE TABLE #TEMPTABLE 
(bookingID INT,
RoomCode VARCHAR(10),
ArrivalDate DATETIME,
DepartureDate DATETIME)

DECLARE @startdate DATETIME
DECLARE @enddate DATETIME
DECLARE @date DATETIME

SET DATEFORMAT dmy

SET @startdate = '19/01/2013'
SET @enddate = '21/01/2013'

SET @date = @startdate

WHILE @date <= @enddate
BEGIN

INSERT INTO #temptable SELECT * FROM bookings
WHERE @date BETWEEN arrivaldate AND departuredate

SET @date = DATEADD(d,1,@date)
END

SELECT DISTINCT * FROM #temptable

Select all Open in new window

Your problem is deceptively complex. There are SEVERAL scenarios to accomodate for.
1) arrival on or after Date1, departs on or before Date2
2) arrival prior to Date1, departure after Date2
3) arrival prior to Date1, departure before Date2
4) arrival after Date1, departure after Date2

With enough time, a person could determine every scenario and write a query with unions that would allow for each of them, but I would build a tally table based on the selected dates and join your data to that table.

Here is an example using your variable tables with your sample data:

declare @temp table (bookingid int, roomcode varchar(5), arrivaldate datetime, departuredate datetime)

insert into @temp values('100033',	'RM-3',	'01/16/2013',	'01/18/2013')
insert into @temp values('100034',	'RM-1',	'01/16/2013',	'01/22/2013')
insert into @temp values('100035',	'RM-1',	'01/27/2013',	'01/29/2013')
insert into @temp values('100036',	'RM-14',	'01/20/2013',	'01/27/2013')
insert into @temp values('100037',	'RM-5',	'01/27/2013',	'01/28/2013')
insert into @temp values('100038',	'RM-6a',	'01/15/2013',	'01/20/2013')

declare @startdate as datetime
declare @enddate as datetime

set @startdate = '01/17/2013'   
set @enddate = '01/21/2013'

declare @tally table (Dt datetime)

WHILE @startdate <= @enddate
Begin
insert into @tally values (@startdate)
set @startdate = dateadd(d,1,@startdate)
End

select 
distinct a.bookingid, a.roomcode, a.arrivaldate, a.departuredate
from @temp a, @tally b where b.dt between a.arrivaldate and a.departuredate 

Select all Open in new window

It pulls back every booking where a person is in a room over the selected date range.

If you know the names of your tables I can post an example that you won't have to modify to run.