Check for dates and times within a range

I have a booking table with the following fields

from_date from_time to_date to_time

I would like to check if a given (from date/time and to date/time) will clash with any existing entries i have in the database. Though there is a time margin to be applied around those dates, in my example i have added 50 minutes to the from time and 80 mintues for the to times.  This is just a safety margin between bookings but important to incorporate.

for example;

2006-08-03   09:00:00     2006-08-05    23:00

in the above example, a user booking could not book on 2006-08-03 at 8:30 or 9:30 and he could also not book at 2006-08-05 at 00:10 or at 22:40.

I also need to return from this query (or in seperate if it must be) the next time slots that are available for each date. e.g. basically suggesting to the user what the closest next time is pre or post any existing times.

I have tried to do this but came across a problem with the times when they overlapped e.g. comparing 23:00 and 01:00 it failed in my BETWEEN clause, so there must be another better way of doing this.

just to recap, i need the query to see if there is a booking space for the gives dates and times, it must allow for a time margin between from and to times and it also must return next suitable timeslots if the query was unsuccessful. for this i basically wrote the query to return no rows if it was successfull.

any more questions please leave a comment,
thanks in advance.

Start Free Trial