You can do this with sql functions too Bob. The sqlite date function has a modifier parameter, eg date(2011-1-11,'+ 1 DAY). MYSQL has the DATE_ADD and DATE_SUB functions which do the same thing. Pete
On Wed, Jan 11, 2012 at 6:55 PM, Bob Sneidar <[email protected]> wrote: > 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 > > > _______________________________________________ > use-livecode mailing list > [email protected] > Please visit this url to subscribe, unsubscribe and manage your > subscription preferences: > http://lists.runrev.com/mailman/listinfo/use-livecode > > -- Pete Molly's Revenge <http://www.mollysrevenge.com> _______________________________________________ use-livecode mailing list [email protected] Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
