检查两个日期是否包含给定的月份

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

我知道你正在寻找一种方法来选择11月相交的所有范围, 在任何一年 .

逻辑如下:

• 如果范围为一年(例如2009年),则开始月份必须在11月之前或等于11月,结束月份必须在11月之后或等于11月

• 如果范围在随后的两年内(例如2009-2010年),开始月份必须在11月之前或等于11月,结束月份必须在11月之后或等于11月

• 如果范围为两年,相差超过1年(如2008-2010年),则11月始终包括在范围内(此处为2009年11月)。

翻译成伪代码,条件是:

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

试试这个:

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

主IDEEA将检查01.11.StartYear和01.11.EndYear日期所在的位置。

希望它有帮助。

筛选在月底之前开始,在月初之后结束的行。2009年10月:

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

或者,只输入月份:

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

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

您可以使用各种功能来实现这一点,例如 DATEPART 和 DATETIFF . 然而,真正的问题不是如何表达给定月份的开始日期或结束日期的条件,而是如何以一种使查询高效的方式来实现这一点。换句话说,如何用一种可裁剪的方式来表达这一点。

如果你搜索一个小的变化表,任何低于10万页,那么它不会有太大的区别,一个完整的扫描可能是完全可以接受的。真正的问题是,表的大小是否很大,完全扫描是不可接受的。

如果在StartDate或EndDate列上没有索引,则没有区别,条件不可搜索,查询将扫描整个表。但是,如果在startdate和enddate上有索引,则表示条件的方式会产生所有差异。日期时间索引的关键部分是必须将搜索表示为精确的日期范围。将条件表示为依赖于日期时间字段的函数将使条件不可搜索,从而导致完整的表扫描。因此,这些知识使自己能够正确地搜索日期范围:

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

这也可以表示为:

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';

哪个查询工作得更好取决于许多因素,如表大小和表中实际数据的统计信息。

不过,你想要十一月从 任何 年份,此查询不提供。解决这个问题的方法违背了程序员的直觉:硬编码相关年份。不管怎样,大多数情况下,表格都有一小部分年份,在过去4-5年的数据范围内,并且计划在3-4年内,直到系统检修:

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';

一年有12个月,写12个单独的程序。这听起来很疯狂吗?当然可以,但从SQL查询编译器和优化器的角度来看,这是最理想的。如何维护这样的代码?12个单独的过程,其中有一个查询本身重复10次(如果使用StartDate和EndDate之间的联合来删除或,则重复20次),120次代码重复, 必须 胡说八道。实际上,不是这样的。使用代码生成来创建过程,比如XML/XSLT,这样您就可以轻松地更改和维护它。客户是否必须了解12个程序并呼叫相应的程序?当然不是,它调用一个包装过程来区分@month参数以调用正确的包装过程。

我再次确认,任何在事实发生后关注系统的人都可能相信这个查询是由一群喝醉的猴子编写的。然而,在参数嗅探、索引可sargability和SQL日期时间怪癖之间的某个地方,其结果是,当它涉及到搜索日历间隔时,这是当今最先进的技术。

哦,如果查询命中 Index Tipping Point 不管怎样,这会使整个争论变得沉默……

更新

顺便说一句,如果您愿意牺牲一些存储空间,还有一种廉价的方法:在 StartMonth AS DATEPART(month, StartDate) 和 EndDate AS DATEPART(month, EndDate) ,并对每个和查询进行索引 WHERE StartMonth = @Month OR EndMonth = @Month (或者再次联合两个查询,一个用于开始,一个用于结束,删除或)。

SQL Server 200/2005,还可以执行以下操作:

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

更新: 远离的 and datepart(yyyy,startDate) = datepart(yyyy,endDate) 是否要指定月份,无论是年还是日?