A
Angelina
Hi,
Im trying to create a stored procedure for my access
project (MSDE).
I having problems with dates in my query.
What i want to do is to select all pieces of equipment
that have been booked given a date period (i.e. start
date and end date.) The user will provide these values on
the interface.
the query i have currently wrote does not pick up
intervals that fall within the equipment booked dates.
e.g the following shows a tuple from my equipment_Booking
table for a piece of ewuipment that has been booked.
Equipment Start_date End_date
TV 3/1/2000 15/1/2000
if the user enters 5/1/2000 as the start date and
10/1/2000 as the end date, the result will not pick up
that this item is booked during that interval.
the same happend when the user enters
start date= 1/1/2000
end date= 20/1/2000
the SQL 'between' function cannot deal with these values.
Is there a date interval function to deal with this
situation?
Heres my code:
SELECT dbo.Equipment_Inv.Equip_Inv_No
FROM dbo.Equipment_Booking
WHERE
(dbo.Equipment_Inv.Rental_Start_Date BETWEEN
@StartDate AND @EndDate) OR
(dbo.Equipment_Inv.Rental_End_Date BETWEEN @StartDate
AND @EndDate)
Can anyone help me finish this query or point me in the
right direction.
any help is appreciated
Im trying to create a stored procedure for my access
project (MSDE).
I having problems with dates in my query.
What i want to do is to select all pieces of equipment
that have been booked given a date period (i.e. start
date and end date.) The user will provide these values on
the interface.
the query i have currently wrote does not pick up
intervals that fall within the equipment booked dates.
e.g the following shows a tuple from my equipment_Booking
table for a piece of ewuipment that has been booked.
Equipment Start_date End_date
TV 3/1/2000 15/1/2000
if the user enters 5/1/2000 as the start date and
10/1/2000 as the end date, the result will not pick up
that this item is booked during that interval.
the same happend when the user enters
start date= 1/1/2000
end date= 20/1/2000
the SQL 'between' function cannot deal with these values.
Is there a date interval function to deal with this
situation?
Heres my code:
SELECT dbo.Equipment_Inv.Equip_Inv_No
FROM dbo.Equipment_Booking
WHERE
(dbo.Equipment_Inv.Rental_Start_Date BETWEEN
@StartDate AND @EndDate) OR
(dbo.Equipment_Inv.Rental_End_Date BETWEEN @StartDate
AND @EndDate)
Can anyone help me finish this query or point me in the
right direction.
any help is appreciated
)