determine if date range falls between another date range - sql

Question

I am trying to find out if there is a way in sql (t-sql preferred) to identify if a date range falls between another date range.

for purposes of my example: daterange1 = i have a defined date range, dates are 1/1/2012 - 1/5/2012 daterange2 = i have two other dates to work with, lets say 1/3/2012 and 1/4/2012

i am trying to have this to use in a CASE statement for something like this

CASE 
    WHEN daterange1 = 0 then result1
    WHEN daterange2 falls within daterange1 then result2 
    END as datestuff 

is this possible in SQL? I'm really stumped on this one, i know how to figure out if a single date falls between a range, but how can it be done with a date range? the answer doesnt necessarily need to be in a CASE statement but it is preferred.

Answer 1

How can you get daterange1 = 0, since it's a range, i.e. 2 values?

The proper test for overlapping dates is

CASE WHEN @range1start <= @range2end
      and @range2start <= @range1end THEN 1 ELSE 0 END

If you mean that daterange2 must fall ENTIRELY within daterange1, then

CASE WHEN @range1start <= @range2start
      and @range2end <= @range1end THEN 1 ELSE 0 END