check time range in store procedure.

I have reservation database, suppose somebody reserved a resource on 10/12/2006 from 9:00am to 12pm. If anybody else want to reserve the same resource from 10am to 3pm. It will not let them reserver. I would like to check a range in store procedure. Is there has any function to check range in easy way?
Many thanks.Check that first timepoint of second reservation attempt is not between
starting and ending timepoint of the first reservation?|||Thank you! I have another question, in the front end, i would like to have a calendar form on it, when user click the date, it would be like 10/12/2006. Also i would like to have time dropdown box, like 8:00am, 9:00am...., How to put together to be the ScheduleDate (10/12/20068:00am), use string then convert to date? In the dropdown box, Is that the format should like 8:00am, or 8am?

Thanks.|||I think you'll have to put the date and the time together
in such a way that the resulting string can be converted
(using convert or cast) to a datetime value.|||Check that first timepoint of second reservation attempt is not between
starting and ending timepoint of the first reservation?

That's a start, but leaves some holes. What if the second reservation starts before the first but ends during or after? It would pass your test but still be a conflict.|||assume
SD = start date of the range to be queried
ED = start date of the range to be queried
FD = from_date of a stored event
TD = to_date of a stored event

here are all the overlap possibilities:
SD ED
| |
1 FD--TD | |
| |
2 FD-|-TD |
| |
3 | FD--TD |
| |
4 FD-|---|-TD
| |
5 | FD-|-TD
| |
6 | | FD--TDyou want to report all events except case 1 and case 6
... where ED >= FD /* eliminates case 6 */
and SD <= TD /* eliminates case 1 */|||Nice visual; helps people see all the possibilities. I use the same solution in my apps.|||to relate this to your example in post #1 ...

stored reservation --
FD = 2006-10-12 09:00
TD = 2006-10-12 12:00

requested reservation --
SD = 2006-10-12 10:00
ED = 2006-10-12 15:00

you are looking at case #2

the query will return a row

when the query returns no rows, it means you can grant the request for a new reservation