SQL date range query help

Customer Support Database Support
1

Hi All,

I have a SQL date range query I need help with.

I have a mysql table called ‘Booking’ that has a number of fields with 2 being of type ‘datetime’ - ‘start_date’
and ‘end_date’.

I want to be able to query the two fields to see if a query date range falls within any of the records.

For example, I have 1 record in the booking table with a start date and time of, 01/01/08 00:00:00 and an end time of 25/01/08 00:00:00. I want to
build a query to say, give me a count of all records that have a booking between 12/12/07 00:00:00 and 05/01/08. In this example, I would get a count of 1.

Because I do not know how to do this, I am getting the total number of days of the query dates and then looping round each day and testing that date
against the record dates. If I find a match, I quit the loop and do not process the new booking. This is totally not the right way of doing it and positive there must be an easier way.

Thank you for looking guys…

P

2

Can’t you just do something like

SELECT * FROM tbl_name
WHERE start_date > ‘12/12/07 00:00:00’
AND end_date < ‘05/01/08 00:00:00’

You would need to be sure to build your date strings according to the locale (date format) used by your MySQL server. For example, today could be 11/6/08 or 6/11/08 depending on whether you’re on this or that side of the pond.

You might also want to check out the date functions at http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

Tim

3

You need to use a query such as the one Skypanther suggest, but you might need to extend the query to catch all clashing bookings.

For example, if you have a potential booking, it may

  1. Start before an exisitng booking and end during an existing booking
  2. Start during an existing booking and end after an existing booking
  3. Start and end during an exisiting booking
  4. Start before and end after an exisisting booking

So just make sure you check for all the variations to make sure that you find any bookings in the DB that cross the potential one.

Cheers.

4

Thank you for your input on this guys and sorry for late reply.

I got it in the end with the below query. Slightly different to Skypanthers suggestion and handles all points mentioned by largerrabbit.

SELECT *
FROM test
WHERE end_date > qstarttime
AND start_date < qendtime

Massive headache to get my head round…

Thanks…:slight_smile: