Check whether two dates contain a given month

Question

My problem is simple... or may be not. I've got a table that contains two dates:

StartDate
EndDate

And I have a constant which is a month. For example:

DECLARE @MonthCode AS INT
SELECT  @MonthCode = 11  /* NOVEMBER */

I need a SINGLE QUERY to find all records whose StartDate and EndDate includes the given month. For example:

/* Case 1 */ Aug/10/2009 - Jan/01/2010
/* Case 2 */ Aug/10/2009 - Nov/15/2009
/* Case 3 */ Nov/15/2009 - Jan/01/2010
/* Case 4 */ Nov/15/2009 - Nov/15/2009
/* Case 5 */ Oct/01/2010 - Dec/31/2010

The first and last case need special attention: Both dates are outside November but the cross over it.

The following query does not take care of case 1 and 5:

WHERE MONTH( StartDate ) = @MonthCode OR MONTH( EndDate ) = @MonthCode

The following query also failed because Aug < Nov AND Nov < Jan = false:

WHERE MONTH( StartDate ) = @MonthCode OR MONTH( EndDate ) = @MonthCode OR (
MONTH( StartDate ) < @MonthCode AND @MonthCode < MONTH( EndDate )
)

Answer 1

I understand that you are looking for a way to select all the ranges that intersect November, in any year.

Here is the logic:

• if the range falls on a single year (e.g. 2009), the start month must be before or equal to November AND the end month after or equal to November

• if the range falls on two subsequent years (e.g. 2009-2010), the start month must be before or equal to November OR the end month after or equal to November

• if the range falls on two years with more than 1 year in difference (e.g. 2008-2010), November is always included in the range (here November 2009)

Translated in pseudo-code, the condition is:

// first case
(
  (YEAR(StartDate)=YEAR(EndDate)) AND
  (MONTH(StartDate)<=MonthCode AND MONTH(EndDate)>=MonthCode)
)
OR
// second case
(
  (YEAR(EndDate)-YEAR(StartDate)=1) AND
  (MONTH(StartDate)<=MonthCode OR MONTH(EndDate)>=MonthCode)
)
OR
// third case
(
  YEAR(EndDate)-YEAR(StartDate)>1
)

Answer 2

DECLARE @MonthCode AS INT
SELECT @MonthCode = 11  /* NOVEMBER */

declare @yourtable table(
    startdate datetime
    , enddate datetime
)
insert into @yourtable(
    startdate
    , enddate
)
(
select '8/10/2009', '01/01/2010'
union all
select '8/10/2009' , '11/15/2009'
union all
select '11/15/2009' , '01/01/2010'
union all 
select '11/15/2009' , '11/15/2009'
union all
select '10/01/2010' , '12/31/2010'
union all
select '05/01/2009', '10/30/2009'
)

select *
from @yourtable
where DateDiff(mm, startdate, enddate) > @MonthCode     -- can't go over 11 months without crossing date
    OR (Month(startdate) <= @MonthCode                  -- before Month selected
            AND (month(enddate) >=@MonthCode            -- after month selected
                OR year(enddate) > year(startdate)    -- or crosses into next year
                )
        )
    OR (Month(startdate) >= @MonthCode                  -- starts after in same year after month
            and month(enddate) >= @MonthCode            -- must end on/after same month assume next year
            and year(enddate) > year(startdate)
        )