> >>I have a date range (start date and end date) supplied by the user
> >>and I also have information in a table that has start dates and
> >>end dates. I want to select everything in the table whose date range
> >>overlaps in any way with the date range given by the user.
> >Well... if you were given a start date of 2003-01-01 and end date of
> >2003-01-31...
> >
> >select *
> > from table_name
> > where start_date between "2003-01-01" and "2003-01-31"
> > or end_date between "2003-01-01" and "2003-01-31"
> Example: start_date = "2002-01-01" and end_date = 2003-02-01".
>
> Instead, if the four dates are s1, e1, s2, and e2 (s=start, e=end), we want:
> s1 <= e2 /* first range can't start after second range ends */
> AND
> s2 <= e1 /* second range can't start after first range ends */
Okay, so s1,e1 are supplied by the user and s2,e2 are in your tables
right? Maybe it's just too early in the morning for me... but I don't
understand the problem anymore.
s1 <= e2 ... yeah that's what my original query does.
e1 >= s2 ... what? yeah... it's an end date.. it's gonna be great than
a start date by nature.
Are you trying to say that you don't want overlap anymore? You want the
s2,e2 to be contained by s1,e1? I doubt that's what you want b/c you
wouldn't have written the list... it's just a small and trivial
alteration to my original query...
select *
from table_name
where (start_date between "2003-01-01" and "2003-01-31"
or end_date between "2003-01-01" and "2003-01-31")
and start_date > "2003-01-01"
and end_date < "2003-01-31"
Sorry, I guess you're gonna have to be more specific. I could probably
sit here for 30 minutes reading your email over and over to
understand... but I would rather you be more clear on what you want.
But then again, I need another cup of coffee in me yet...
Matt.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
