Re: date ranges

[Bruce Feist]

Matt Gostick wrote:

Sarah Heffron wrote:

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. 
       

Matt Gostick's proposed solution:

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"
     

Bruce Feist's response:

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 */
   

Matt's response to Bruce's response:

Okay, so s1,e1 are supplied by the user and s2,e2 are in your tables
right?  

That's irrelevant to the form of the condition, but yes.

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.
But it's an end date from another range.  There's not necessarily any
relationship between the two.
Your original suggestion works if the two ranges partially overlap, but
not if one is wholly contained in the other.
Bruce Feist





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]