Date intersection revisited

[Pete]

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 <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.
>
> Bob
>
>
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> 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>