Checking inbetween dates?? hellp

hedgehead

Hey, I was wondering if someone could help me, I am trying
to do something really simple here, but cant get it working at all

I have these two dates stored in a db with a room id of:
105
The first is 06/02/2005 [DateCheckin]
The second is 06/25/2005 [DateCheckout]

This is for bookings a room, so i have a count function that will count where room = 105 and if the room is booked...
With this SQL i dont get a count result of 1, but i get 0.
What i need is the sql to check through every date (as seen below) and compare it with the dates inputted

It doesnt seem to be checking inbetween the dates. Could someone please help me??

06/03/2005 is the first date to be compared with
06/25/2005 is the second date to be compared with

SELECT COUNT(*) AS bookings_count
FROM hotel.bookings
WHERE RoomID = '105'
AND DateCheckin between '06/03/2005' and '06/25/2005'
AND DateCheckout between '06/03/2005' and '06/25/2005'

edcellgavin

given your data,

The first is 06/02/2005 [DateCheckin]
The second is 06/25/2005 [DateCheckout]

your code will evaluate

SELECT COUNT(*) AS bookings_count
FROM hotel.bookings
WHERE RoomID = '105'
AND 06/02/2005 between '06/03/2005' and '06/25/2005'
AND 06/25/2005 between '06/03/2005' and '06/25/2005'

to false since
06/02/2005 does not fall between '06/03/2005' and '06/25/2005'.
and 06/25/2005 lies at the boundary of your range, not within(between).

mrhappiness

DateCheckout between '06/03/2005' and '06/25/2005' evaluates to true (if mysql recognises 06/25/2005 as 2005-06-25)

leatherback

Hi,

I am not sure. I think it is evaluating the dates in your query as strings. try using a timestamp in the query (maketime and date() functions)

Sxooter

Are DateCheckin and DateCheckout both datetime or timestamp types? If they're plain text, you'll have to do some casting...