Select row between certain range of date

Question

Possible Duplicate:
How to look which room is available between 2 dateTimes

I have this row of data

|room_id|checkin     |checkout  |
+       +            +          +
|1      |2012-05-02  |2012-05-09|

But I don't want to choose this row which betweeen two dates

I have this query

SELECT * 
FROM `reservations`  `res` 
WHERE (2012-05-03 
   NOT BETWEEN res.expected_checkin_date AND res.expected_checkout_date) 
 AND COALESCE(2012-05-08 
   NOT BETWEEN res.expected_checkin_date AND res.expected_checkout_date)

However, I am still getting this row which is not suppposed to come up. Actually, my idea is to choose all rows which is not between these two date. Any idea?

Update 1

Basically, someone is trying to reserve a room from 5/3 to 5/8 and I want to see a list of rooms that would meet that criteria.

is checkin, checkout and the value you're passing in all DATE data types? or are you doing comparison at a string level? What is that coalese doing? it's always going to be 2012-05-08, and I'm surprised you're not getting a syntax error as I thought it required at least 2 parameters. — xQbert, May 19 '12 at 14:21
Yes, it is DATE type and not I am not doing comparision in string level — Akram, May 19 '12 at 14:27
Lastly, what are you trying to do here? What's the functional requirement. I'm not following the technical example you have.. Are you wanting all rooms which would be vacant for the dates specified? Basically, someone is trying to reserve a room from 5/3 to 5/8 and you want to see a list of rooms that would meet that criteria? If so wouldn't you need to join back to rooms since there wouldn't be a reservation in the system? — xQbert, May 19 '12 at 14:29
This question has been asked and answered several times already on SO: leverage the existing knowledge: Similar to: http://stackoverflow.com/questions/5615225/hotel-reservation-system-sql-identify-any-room-available-in-date-range or http://stackoverflow.com/questions/10511205/how-to-look-which-room-is-available-between-2-datetimes — xQbert, May 19 '12 at 14:37

score 1 · Accepted Answer · answered May 19 '12 at 14:35

Don't know what types are checkin and checkout, whether strings or dates/datetimes, the issue seems to have to do with the fact your date constants are not delimited with single quotes (i.e. they are not represented as strings, as they should have been.)

So, just try the following instead of your query:

SELECT * 
FROM `reservations`  `res` 
WHERE ('2012-05-03' 
   NOT BETWEEN res.expected_checkin_date AND res.expected_checkout_date) 
 AND COALESCE('2012-05-08' 
   NOT BETWEEN res.expected_checkin_date AND res.expected_checkout_date)

Thank you, it was because of this. It works – Akram May 19 '12 at 14:49 — Akram, May 19 '12 at 14:49

score 0 · Answer 2 · answered May 19 '12 at 14:38

0

I suspect you want:

SELECT * 
FROM reservations AS res 
WHERE res.expected_checkout_date <= '2012-05-02'
   OR '2012-05-09' <= res.expected_checkin_date

answered May 19 '12 at 14:38

ypercubeᵀᴹ

113,259
19
174
235

Their conditions are inclusive and they want the dates to be *outside* the inclusive ranges, i.e. I think your `<=`s should probably be `<`s. – Andriy M May 19 '12 at 14:42
@Andriy: Yes but you have `[3,8]` range, I have `2` to `9`. – ypercubeᵀᴹ May 19 '12 at 14:46
Oops, I didn't notice you switched the constants, sorry! But why did you do that? They might be script arguments, you know. – Andriy M May 19 '12 at 14:48

Select row between certain range of date

Update 1

2 Answers2