[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