Search for rows between start and end dates

Databases
1

Hi,

I’m in the middle planning a simple holiday home booking system, and have been working out what query’s are needed, I’ve worked out most of them but cant seem to work this one out.

I have got a simple table which looks likes:

ID| start | end | price
1 | 2010-04-10 | 2010-04-17 | 20.00
2 | 2010-04-17 | 2010-04-24 | 25.00
3 | 2010-04-24 | 2010-05-08 | 10.00

I’m looking for a query which returns all the rows/records which fulls between a search criteria, for example:
start date: 2010-04-15
end date: 2010-04-20
Would return row 1 and 2

or

start date: 2010-04-24
end date: 2010-04-30
Would return row 3

Any help would be much appreciated

2

you might need to explain that a bit more

in your first example, the given range overlap rows 1 and 2, but only partially

but your second example, doesn’t the given range partially overlap row 2?

3

Hello,
Can you please check if the following helps you to solve your problem

declare @start datetime, @end datetime
select @start = '2010-04-24', @end = '2010-04-30'
select * from testtable 
where 
(start < @start and @start < [end]) OR
(start < @end and @end < [end])