Code:
ALTER PROCEDURE GetBookedResource
(
@StartDate datetime,
@EndDate datetime,
@Resource char(30)
)
AS
SELECT *
FROM tblBookings
WHERE StartDate >= @StartDate and EndDate <= @EndDate and
Resource=@Resource
__________________________________________________ ______________________
_____________________________
The SP I'm using above is used to find out if a resource is free for a
particular period of time.
The time at my workplace is split into 6 periods....
P1 starts at 9am and ends at 10am.....
P2 starts as 10.01am and ends at 11am...e.t.c.
When Bookings are added...I append the time to the Start/End Date
depending on what period the end user has selected..
e.g..
Quote:
If end user has booked a resource for today at P1 then I would have
appended 9am to StartDate and 10am to the EndDate before I pass both the
StartDate and EndDate to the SP.
If end user has booked a resource from today at P1 to tomorrow at P2
then I would have appended 9am to StartDate and 11am to the EndDate
before I pass both the StartDate and EndDate to the SP.
The Problem
The SP only returns the correct data when the @StartDate and @EndDate
are on the same day and period
e.g..
Quote:
@StartDate=Today 9AM and @EndDate=Today 10AM
It doesn't return the current data when @StartDate and @EndDate are on
the same day but on different periods
e.g..
Quote:
@StartDate=Today 9AM and @EndDate=Today 11AM
Nor does it return the correct data when @StartDate and @EndDAte on on
different Days
e.g.
Quote:
@StartDate=Today 9AM and @EndDate=Tomorrow 11AM
Can anyone explain why that is??
*** Sent via Developersdex
http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!