CODEWITHSUNDEEP
phpmysql
Suman Halder
Suman Halder

Reputation: 27

I want to search between two dates in sql

I have two tables and this table name is "rooms" Table Rooms and other one is "bookings" !Table Bookings

I joined two tables now, I want values when i will search between

book_form = "2016-12-30" and book_to = "2016-12-31"

it will be return true because this two dates does not exists in the "bookings" table, and when search between

book_form = "2016-12-30" and book_to = "2017-01-05" or book_form = "2017-01-03" and book_to = "2017-01-15"

it will be return false because this date exists in bookings table.

This is my query.

select * from rooms join room_book on rooms.room_id = room_book.room_id 
where status = 'available' and room_book.book_from NOT BETWEEN '2016-12-30' 
AND room_book.book_to NOT BETWEEN '2016-12-31'

Upvotes: 1

Views: 410

Answers (2)

Pie
Pie

Reputation: 351

The answer from Kishan Patel returns all records between two dates. So if you want a true or false if a room is available between two dates, you could do something like this:

SELECT IF(COUNT(*) > 0, TRUE, FALSE) AS NewResult
from rooms 
join room_book on rooms.room_id = room_book.room_id
where status = 'available' 
and room_book.book_from >= '2016-12-30' AND room_book.book_to <= '2016-12-31'

Upvotes: 1

Kishan Patel
Kishan Patel

Reputation: 485

Try this one return all record between two dates

select * from rooms 
join room_book on rooms.room_id = room_book.room_id
where status = 'available' 
and room_book.book_from >= '2016-12-30' AND room_book.book_to <= '2016-12-31'

make sure its useful to you

Upvotes: 0

Related Questions