Date intersection revisited
pete at mollysrevenge.com
Wed Jan 11 21:55:55 CST 2012
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.
On Wed, Jan 11, 2012 at 6:55 PM, Bob Sneidar <bobs at twft.com> 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.
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
Molly's Revenge <http://www.mollysrevenge.com>
More information about the use-livecode