Select CONVERT(varchar(100), GETDATE(), 120): 2006-05-16 10:57:49
check in the booking table if the date matches the checkin and checkout dates selected by users.
first:
SELECT *
FROM Room
LEFT JOIN booking ON (Room.RoomId = booking.RoomId)
WHERE booking.Roomid is null
AND CONVERT(varchar(100), GETDATE(), 120) BETWEEN bookin.checkindate '" + TxtCheckIn.Text + "'
AND booking.checkoutdate '" + TxtCheckOut.Text + "'" + "
ORDER BY Room.RoomType
second:
If it doesn't match, the query should show all rooms in the room table (even if it is in the booking table), provided that they have different dates.
select * from Room where ......