[thelist] SQL Dates Problem

Ken Schaefer Ken at adOpenStatic.com
Tue Apr 12 08:42:30 CDT 2005


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: thelist-bounces at lists.evolt.org [mailto:thelist-
: bounces at lists.evolt.org] On Behalf Of Burhan Khalid
: Subject: [thelist] SQL Dates Problem
: 
:    I'm sure there is a better way to do this.  Can anyone suggest a
: better approach?  Perhaps there is some SQL magic that can get me the
: correct number in one try?  I'm using MySQL.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I could write you something, but I don't think it'll work in mySQL, unless
mySQL supports subselects now.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
:    I'm also using a similar rudimentary approach to 
: finding out of a new event conflicts with any event 
: in a hall.  For this, I'm getting all the start/end 
: date pairs for each hall's events, then checking if the new
: event's date pairs conflict.  The idea here is that 
: if an event's start and end dates are in between 
: start and end dates for another event, then
: they conflict.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   |------Event------|
|-----|            |------|
       |--------|
 |----------------------|

The above diagram shows all the possible ways a proposed event would conflict
with an existing event (use a monospaced font to view helps)

If you'll notice, every conflicting event has a start date (or time) prior to
the existing event's end date and an end date after the existing event's
start date.

So

SELECT (whatever)
FROM Events
WHERE (newEventStartDate) < EventStartDate
AND (newEventEndDate) > EventStartDate

would return any conflicting, existing events. That query will work in mySQL
(at least something does...) :-)

Cheers
Ken


More information about the thelist mailing list