1

I am developing an application is room booking. First I want to show the available rooms to the user based on his city and fromdate and todate. For this I have a table tbl_room with columns like id, room_name, city, fromdate, todate and so on.

I am trying to write a query based on fromdate and todate and city for showing available rooms from my table.

My query looks like this :

select 
    rooms, price, name, persons_capacity 
from 
    tbl_room 
where 
    city = 'xxxx' 
    and fromdate between 'yyyy-mm-dd' and 'yyyy-mm-dd'

But this returns wrong results because I am not checking todate here.

How can I use todate in my query for checking availability rooms?

Thanks.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Nag
  • 489
  • 3
  • 6
  • 27
  • This might help: http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap – Felix Pamittan Feb 18 '16 at 08:06
  • Well, are you storing the **availability** of the room (as `from - to` periods), or are you storing when the room is **occupied**? – marc_s Feb 18 '16 at 08:27
  • Yes,i am storing availability of the rooms in my table tbl_room in back-end. And the good question you are asked. My main problem here is can i take another table for booked rooms?Suppose if i take what is the common field between tbl_room table and my booked table?can you please tell me?i am new to this. thanks @marc_s – Nag Feb 18 '16 at 09:32

1 Answers1

1

You can do it like this:

select rooms,price,name,persons_capacity 
from tbl_room 
where city='xxxx' 
and fromdate <= @end and enddate >= @start

With @start to @end being your daterange.

Carra
  • 17,808
  • 7
  • 62
  • 75
  • why you checkd fromdate<=todate and enddate>=fromdate?i confused here thanks – Nag Feb 18 '16 at 08:15
  • 1
    I see it as: You declare a variable `@end` as your `todate` and a variable `@start` as your `fromdate`. You pass to the `@start` the `fromdate` and to `@end` your `todate`. Now you have the ranges defined. And Carra checks that the end date is not before the start date and the start date must be before the end date. – CM2K Feb 18 '16 at 09:03
  • For more info on the why, you can take a ook at the comment made by Felix Pamittan. – Carra Feb 18 '16 at 09:42