Date intersection revisited

[Bob Sneidar]

Hi all. A while ago someone asked how to phrase a query for date intersections. I have been struggling all day to come up with what is probably very simple logic for date intersections. For instance, I need to find all the reserved rooms whose dates fall within the selected dates of a new reservation. 

I came up with a way to do that. It's a bit more complicated than using the SQL between function because BETWEEN is inclusive, and if you think about it, it's fine if a new reservation falls on the departure day of an existing one. 

So given that startdate and enddate are SQL columns in a reservation database, and the literal dates are the new reservation start and end dates, I give you  this where clause:

(startdate >= "2012-1-11" AND startdate < "2012-1-14") OR (enddate > "2012-1-11" AND enddate <= "2012-1-14")

This in effect allows new reservations to begin on the day another ends, or end on the day another begins. I don't know why this kind of logic comes so hard for me sometimes. I have to draw pictures to sort it out. At any rate, if I can prevent someone else from having to go through the mental hoops I did, then I feel I have contributed. 

Bob