MSSQL Check date in between 2 column of dates?

[VRao]

Hi,I have a table that having 2 separate date field. 1 is FromDate and another is ToDate. I use below stored procedure code to get the date.CREATE PROCEDURE [dbo].[ProcGetDateAvai_Hotel1] @FromDate datetime, @ToDate datetimeASBEGINSET NOCOUNT ON;SELECT DISTINCT ID, HotelIDFROM hotelreservation AS HRWHERE HR.fromdate >= @FromDate AND HR.todate <= @ToDateENDThe table for hotelreservation records shown below.ID...... HotelID...... FromDate...... ToDate....... CustID1....... 10001....... 1/2/2008....... 1/4/2008...... 100012....... 10001....... 1/5/2008....... 1/10/2008.... 10002If i query FromDate = '1/2/2008' to ToDate = '1/4/2008' then the result is ok which means "Hotel is booked". But if i query FromDate = '1/6/2008' to ToDate = '1/9/2008' it says "Hotel Available" whereas that date is not available in the table because data no. ID 2 is already booked.Is there any way can this thing solve?Please i need desperate solution. Thx.

[andylah]

just replace the >= , <= with between so it like this SELECT DISTINCT ID, HotelIDFROM hotelreservation AS HRWHERE HR.fromdate between @FromDate AND @ToDate AND HR.todate between @FromDate AND @ToDateENDif i'm not wrong but i know sure u must use between if u wanna compare datetime type