• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 524
  • Last Modified:

determine if date ranges overlap

I have a table that has a startdate and enddate field.  I want to create a function that accepts a start and end date parameter and compare these dates against the dates in the table to see if there are any overlapping days.  If there are overlapping days then I want the function to return False.

It shouldn't be hard but it's 5:00 on a Friday and my brain is mush.
0
jayh
Asked:
jayh
1 Solution
 
Brendt HessSenior DBACommented:
Here's one that should work (you'll have to convert it into a function - I don't have SQL2K to work with)

NOTE:  I recommend returning False for No Overlap - this allows this select to work, since Count would be 0:

SELECT Count(*) From MyTable
Where StartDate BETWEEN @Start And @END
   OR EndDate BETWEEN @Start And @End
   OR @Start Between StartDate AND EndDate


Given StartDate 5/1/2001   EndDate 5/15/2001

@Start 4/30/2001  @End 5/1/2001  Count = 1
@Start 5/15/2001  @End 5/17/2001  Count = 1
@Start 4/30/2001  @End 5/17/2001  Count = 1
@Start 5/16/2001  @End 5/17/2001  Count = 0
0
 
Éric MoreauSenior .Net ConsultantCommented:
something like:

select *
from table1
where startdate between date1 and date2
or enddate between date1 and date2



date1 and date2 are the 2 fields in your table
startdate and enddate are the values given by the user
0
 
jayhAuthor Commented:
Close enough...
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now