datetime question - need to check if date falls in range

[webslinga]

Hey all,
i have two fields in a booking table, one called arrival date and the other called departure date. I need to check if a user inputted date falls within those two fields ranges.

arrival departure
-----------------------|------------------------
2006-11-10 13:00:00 2006-11-21 13:00:00

SELECT COUNT(booking.BOOKINGS_UNID) FROM booking WHERE booking.PROPERTY_ID=654 AND booking.ARRIVAL>='2006-11-15 00:00:00' AND booking.DEPARTURE <= '2006-11-15 23:59:59'

This is all I have but obviously this will not work because the departure date is in a range that is not. What I'd like to do, is determine if the date falls in the range of the arrival and departure date.

BTW, don't worry about how the date gets input. That's taken care of using PHP.

 

[MillerH]

What your asking for is so easy that I swear there must be something I'm not understanding:

SELECT COUNT(booking.BOOKINGS_UNID) FROM booking WHERE booking.PROPERTY_ID=654 AND booking.ARRIVAL[COLOR=blue]<=[/color]'Your Date Here' AND 'Your Date Here'[COLOR=blue]<=[/color]booking.DEPARTURE

 

[TonyGroves]

The only problem I see there is that the <= and >= should be swapped (as MillerH points out). You might also like to shorten it to something like:
[tt]
SELECT COUNT(booking.BOOKINGS_UNID)
FROM booking
WHERE
booking.PROPERTY_ID=654
AND $date BETWEEN booking.ARRIVAL AND booking.DEPARTURE
[/tt]

 

[webslinga]

There's got to be something wrong with the previous post. This will get everything earlier than the arrival date and greater than the departure date - exactly the opposite of what my query should be.

 

[webslinga]

Ooops, wrote too late.

 

[webslinga]

SELECT COUNT(booking.BOOKINGS_UNID) FROM booking WHERE booking.PROPERTY_ID=654 AND '2006-11-15' BETWEEN booking.ARRIVAL AND booking.DEPARTURE

The following code does not return any results - and I know I have a result set with today's datetime value.

 

[MillerH]

The alligator (<) or (>) eats the bigger animal. Cuz it's hungry, ya know?

1 < 3 is true
1 > 3 is false
2 <= 2 is true
2 <= 3 is true
2 <= 1 is false

booking.ARRIVAL<='Your Date Here' = your date after ARRIVAL date.

Here endith the lesson.

 

[MillerH]

Per:

http://www.mysql.org/doc/refman/5.1/en/comparison-operators.html

Make sure that your types match. If ARRIVAL and DEPARTURE are DATETIME's, so should be the value that you are testing with.

SELECT '2006-11-15' BETWEEN '2006-11-15 00:00:00' AND '2006-11-18 00:00:00';
returns FALSE

SELECT '2006-11-15 00:00:00' BETWEEN '2006-11-15 00:00:00' AND '2006-11-18 00:00:00';
returns TRUE

You can true using the CAST operator to convert your value. But I was having trouble getting it to work on a constant, so it might just be easier to add the time data yourself.

http://www.mysql.org/doc/refman/5.1/en/cast-functions.html

 

[webslinga]

I am not explaining the problem correctly I think. BTW MillerH, I could do without the condescending remarks.

First of all, I only have one date to work with. So all i am doing to check if today's date falls within the date range of arrival and departure is

$dayStart = date('Y-m-d 00:00:00');
$dayEnd = date('Y-m-d 23:59:59');

According to you, the following should work:

SELECT COUNT(vrs_booking.BOOKINGS_UNID) FROM vrs_booking WHERE vrs_booking.PROPERTY_UNID=654 AND vrs_booking.ARRIVAL<=$dayStart AND vrs_booking.DEPARTURE>=$dayEnd

BTW TonyGroves, I cannot use the BETWEEN due to reasons that would take me too long to explain.

Guys, I know when I get to the answer, I'll have a 'DUH!' moment but until then, please help me along.

 

[MillerH]

$dayStart = date('Y-m-d 00:00:00');
$dayEnd = date('Y-m-d 23:59:59');

Just a guess. But could the 'Y-m-d' be your problem?

 

[webslinga]

DUH!

Guys, I got it. My head is clear. My spirit is lifted.

It turns out that I was not setting the date constraints correctly.

Sorry to waste time.

 

[MillerH]

Just a guess. But could the 'Y-m-d' be your problem?

I'm guessing now that is part of php. I don't know php.

I would suggest that you tail the SQL log to see what query is being passed to the server just as a sanity check. Maybe the values that you are passing are not what you expect. Or if you believe the query should be returning results, duplicate the query at the sql prompt, and see what it returns. Tweak the query by removing conditions until you get results and see what the most limitting condition is.

Good luck.