Check whether two dates contain a given month

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

StartDa开发者_运维知识库te
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 )
)

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
)

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)
        )

Try this:

select * from Mytable
where 
month(StartDate) = @MonthCode or month(EndDate) = @MonthCode // Nov/15/2009 - Nov/15/2009
or
dateadd(month,@MonthCode-1,convert(datetime,convert(varchar,year(StartDate))))
between StartDate and EndDate // Oct/01/2010 - Dec/31/2010
or
dateadd(month,@MonthCode-1,convert(datetime,convert(varchar,year(EndDate))))
between StartDate and EndDate // Dec/01/2009 - Dec/31/2010 - tricky one

The main ideea is to check where are 01.November.StartYear and 01.November.EndYear dates located.

Hope it helps.

Filter for the rows that start before the end of the month, and end after the start of the month. For October 2009:

select *
from YourTable
where StartDate < '2009-11-01' and EndDate >= '2009-10-01'

Or, with just the month as input:

declare @month datetime
set @month = '2009-10-01'

select *
from YourTable
where StartDate < dateadd(month,1,@month)
and EndDate >= @month

There are various functions you can use to achieve this, like DATEPART and DATETIFF. However, the real problem is not how to express the condition of StartDate or EndDate falling on the given month, but how to do this in a fashion that makes the query efficient. In other words how to express this in a SARGable fashion.

In case you search a small change table, anything under 10k pages, then it doesn't make that much of a difference, a full scan would be probably perfectly acceptable. The real question is if the table(s) are significant in size and a full scan is unacceptable.

If you don't have an index on any of the StartDate or EndDate column it makes no difference, the criteria is not searchable and the query will scan the entire table anyway. However, if there are indexes on StartDate and EndDate the way you express the condition makes all the difference. The critical part for DATETIME indexes is that you must express the search as an exact date range. Expressing the condition as a function depending on the DATETIME field will render the condition unsearchable, resulting in a full table scan. So this knowledge render itself to the correct way searching a date range:

select ... from table
where StartDate between '20091101' and '20091201'
or EndDate between '20091101' and '20091201';

This can be also expressed as:

select ... from table
where StartDate between '20091101' and '20091201'
union all
select ... from table 
where EndDate between '20091101' and '20091201'
and StartDate not between '20091101' and '20091201';

Which query works better depends on a number of factors, like your table size and statistics of the actual data in the table.

However, you want the month of November from any year, which this query does not give you. The solution to this problem is against every instinct a programmer has: hard code the relevant years. Most times the tables have a small set of years anyway, something in the range of 4-5 years of past data and plan for 3-4 years more until the system will be overhauled:

select ... from table
where StartDate between '20051101' and '20051201'
or EndDate between '20051101' and '20051201'
union all
select ... from table
where StartDate between '20061101' and '20061201'
or EndDate between '20061101' and '20061201'
union all
...
select ... from table
where StartDate between '20151101' and '20151201'
or EndDate between '20151101' and '20151201';

There are 12 months in a year, write 12 separate procedures. Does this sound crazy? It sure does, but is the optimal thing from the SQL query compiler and optimizer perspective. How can one maintain such code? 12 separate procedure, with a query that repeats itself 10 times (20 times if you use the UNION between StartDate and EndDate to remove the OR), 120 repeats of code, it must be non-sense. Actually, it isn't. Use code generation to create the procedures, like XML/XSLT, so you can easily change it and maintain it. Does the client has to know about the 12 procedures and call the appropriate one? Of course not, it calls one wrapper procedure that discriminates on the @Month argument to call the right one.

I recon that anyone who will looks at the system after the facts will likely believe this query was written by a band of drunk monkeys. Yet somewhere between parameter sniffing, index SARGability and SQL DATETIME quirks the result is that this is the state of the art today when it pertains to searching calendar intervals.

Oh, and if the query hits the Index Tipping Point it will make the whole argument mute anyway...

Update

BTW there is also a cheap way out if you're willing to sacrifice some storage space: two persisted computed columns on StartMonth AS DATEPART(month, StartDate) and EndDate AS DATEPART(month, EndDate), and index on each and query WHERE StartMonth = @Month OR EndMonth = @Month (or again UNION between two queries one for Start one for End, to remove the OR).

SQL Server 200/2005, You can also do this:

select 
   * 
from 
   table
where 
   datepart(m,startDate) = 11
   and datepart(m,EndDate) = 11

UPDATE: Removed and datepart(yyyy,startDate) = datepart(yyyy,endDate) Do want a given month regardless of Year or Day?