determine if date ranges overlap

Posted on 2001-08-03
Medium Priority
Last Modified: 2011-10-03
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.
Question by:jayh
LVL 32

Accepted Solution

Brendt Hess earned 300 total points
ID: 6350571
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
LVL 71

Expert Comment

by:Éric Moreau
ID: 6350573
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

Author Comment

ID: 6356117
Close enough...

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question