0

Possible Duplicate:
Determine Whether Two Date Ranges Overlap

I am trying to create a query that selects all the cars in one table based on whether they have a booking in the bookings table. At the moment i have this which works well until if i put a booking from date minus one day and a booking two date plus one day. Because i am using equals it will show a car which is booked within that date range.

How would i alter this so if i have a car in the bookings table with a from date of 22-jan-13 and a to date of 25-jan-13 and a user inputs the from a to dates of 20-jan-13 and 27-jan-13 respectively, the car will not show up in the results.

SELECT   *
FROM vehicles, car_model, manufacture
WHERE NOT EXISTS (SELECT *
                  FROM booking
                  WHERE vehicles.reg_number = booking.reg_number
                  AND booking.date_from = '22-JAN-13'
                  AND booking.date_to = '23-JAN-13'
                  AND booking.booking_status = 1)
AND vehicles.model_code = car_model.model_code
AND car_model.manufacture_code = manufacture.manufacture_code;

Thanks.

Community
  • 1
  • 1
user667430
  • 1,487
  • 8
  • 38
  • 73
  • what type are date_from and date_to? datetime or varchar? – Marek Musielak Jan 22 '13 at 11:10
  • The format for them is date – user667430 Jan 22 '13 at 11:12
  • Rather than this being a specific SQL problem, it sounds more like a general problem of determining when two date ranges overlap. Does the Stackoverflow question [http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap](http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap) help any? – PeteH Jan 22 '13 at 11:11
  • Thanks for your help. That was the answer to my problem. – user667430 Jan 22 '13 at 11:17

1 Answers1

0

Use BETWEEN operator - SELECT ... WHERE ... BETWEEN start_date AND end_date...

Anders R. Bystrup
  • 15,729
  • 10
  • 59
  • 55
Art
  • 5,616
  • 1
  • 20
  • 22