Проверьте, содержат ли две даты данный месяц

Попробуйте следующее:

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

Основная идея - проверить, где находятся даты 01.November.StartYear и 01.November.EndYear.

Надеюсь, это поможет.

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

SQL Server 200/2005. Вы также можете сделать это:

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

ОБНОВЛЕНИЕ: Удалены и datepart (yyyy, startDate) = datepart (yyyy, endDate) . Хотите ли вы конкретный месяц независимо от года или дня?

Я понимаю, что вы ищете способ выбрать все диапазоны, которые пересекают ноябрь, в любом году .

Вот логика:

• , если диапазон попадает в один год (например, 2009), начальный месяц должен быть раньше или равным ноябрю И месяцем окончания после или равным ноябрю

• , если диапазон попадает на два последующих года (например, 2009- 2010), начальный месяц должен быть до или равным ноябрю ИЛИ месяцем окончания после или равным ноябрю

• , если диапазон попадает на два года с разницей более чем на 1 год (например, 2008-2010), ноябрь всегда включается в диапазоне (здесь ноябрь 2009 г.)

В переводе псевдокодом условие выглядит следующим образом:

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

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