Determine Date Range Falls between other Date Range

I need to figure out how to see if a date in a date range falls between
another date range. For instance - I need to know if any date between
3/1/06 and 3/31/06 falls between a date range of 2/16/03 to 4/1/06.
Does anyone know of a way to code that without using a cursor to go
through every day to see if that day of the month is between the other
date range?
Melissambonsted@.yahoo.com wrote:
> I need to figure out how to see if a date in a date range falls between
> another date range. For instance - I need to know if any date between
> 3/1/06 and 3/31/06 falls between a date range of 2/16/03 to 4/1/06.
> Does anyone know of a way to code that without using a cursor to go
> through every day to see if that day of the month is between the other
> date range?
> Melissa
Here's some sample data and a query:
CREATE TABLE tbl (dt_from DATETIME NOT NULL, dt_to DATETIME NOT NULL,
CHECK (dt_from <= dt_to), PRIMARY KEY (dt_to));
INSERT INTO tbl VALUES ('20060301', '20060331');
DECLARE @.dt_from DATETIME, @.dt_to DATETIME ;
SET @.dt_from = '20030216';
SET @.dt_to = '20060401';
SELECT dt_from, dt_to
FROM tbl
WHERE dt_from >= @.dt_from
AND dt_to <= @.dt_to ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On 1 Apr 2006 14:21:55 -0800, David Portas wrote:

>mbonsted@.yahoo.com wrote:
>Here's some sample data and a query:
>CREATE TABLE tbl (dt_from DATETIME NOT NULL, dt_to DATETIME NOT NULL,
>CHECK (dt_from <= dt_to), PRIMARY KEY (dt_to));
>INSERT INTO tbl VALUES ('20060301', '20060331');
>DECLARE @.dt_from DATETIME, @.dt_to DATETIME ;
>SET @.dt_from = '20030216';
>SET @.dt_to = '20060401';
>SELECT dt_from, dt_to
> FROM tbl
> WHERE dt_from >= @.dt_from
> AND dt_to <= @.dt_to ;
Hi David,
This will only find date ranges completely embedded in another date
range. To find any overlap, change it to
SELECT dt_from, dt_to
FROM tbl
WHERE dt_from <= @.dt_to
AND dt_to >= @.dt_from ;
(Change <= and >= to < and > if one range starting the same day that
another range ends is not considered an overlap)
Hugo Kornelis, SQL Server MVPsql