views:

54

answers:

0
Declare @str DateTime, @End DateTime, @CatID Int
Set @str = '24 Sep 2010 8:30 AM'
Set @end = '24 Sep 2010 1:30 PM'
Set @CatID = '3'

Select b.ID, b.BookingID, Name, r.ResID, r.ResName
, DATEADD(day, 0, DATEDIFF(day, 0, StrDate)) + DATEADD(day, 0 - DATEDIFF(day, 0,    StrTime), StrTime) as StrDate
, DATEADD(day, 0, DATEDIFF(day, 0, EndDate)) + DATEADD(day, 0 - DATEDIFF(day, 0, EndTime), EndTime) as EndDate
, RequestID, RequestDate, BookingStatus 
From tbl_Booking b, tbl_BookingResources br, tbl_Resources r
Where b.BookingID = br.BookingID
and br.ResID = r.ResID
and r.CatID = @CatID
AND 
(
    (ENDDATE BETWEEN CAST(@str AS DATETIME) AND CAST(@end AS DATETIME) ) OR
    (STRDATE BETWEEN CAST(@str AS DATETIME) AND CAST(@end AS DATETIME) )OR
    (CAST(@str AS DATETIME) BETWEEN STRDATE AND ENDDATE) OR (CAST(@end AS DATETIME) BETWEEN STRDATE AND ENDDATE)
)
AND EndDate >= b.RequestDate

This is my record in table:

RB00008 Long Time Booking [Edited] Big Projector 9/20/2010 1:00:00 PM 9/24/2010 4:00:00 PM Chit Min Maung Sep 20 2010 1:00PM ,Sep 24 2010 4:00PM   

So when I run this stored procedure, should I get 1 record, right? But I can't... Why? In my table I have 20 sep 2010 1 pm to 24 Sep 2010 4 pm, so i use 24 sep 2010 8 am to 1:30 pm... I must get 1 record... but I can't