SQL日期范围查询

Question

在我的表格中，我有118条记录详细介绍了项目。我在这里关注的两个字段是startdate和enddate。

我需要从这个视图中生成一个报告，显示在以下日期范围内哪些项目是“活动”的:

01/01/2011-01/12/2011

我尝试了以下WHERE子句:

WHERE startdate BETWEEN '01/04/2011' AND '01/12/2011' 
OR enddate BETWEEN '01/04/2011' AND '01/12/2011'
OR startdate <= '01/04/2011' AND enddate >= '01/12/2011'

看起来这个查询结果不正确，只显示了少数记录，而我知道实际上应该显示很多记录，比如一个开始日期为2011年7月20日，结束日期为2011年11月21日的项目在运行WHERE查询时就消失了。

有人能看出这个WHERE查询存在什么问题吗？

- JsonStatham

---

有点儿愚蠢的问题，但是startdate和enddate的数据类型是什么？ - billinkc

使用VARCHAR将格式转换为文本：convert(VARCHAR,dbo.tbl_project_data.startdate, 103) AS startdate, convert(VARCHAR,dbo.tbl_project_data.enddate, 103) AS enddate, - JsonStatham

表中的基础数据类型是什么？不是你转换后的类型，而是基本类型？SELECT ISC.TABLE_SCHEMA、ISC.TABLE_NAME、ISC.COLUMN_NAME、ISC.DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS ISC WHERE ISC.column_name IN ('startdate'、'enddate') - billinkc

请记住，SQL将时间作为日期的一部分存储，因此如果您使用GetDate（）或类似函数来填充字段值，则还会包含时间部分。 1/12/2011被认为是2011年1月12日午夜。 如果一个日期字段是在1月12日上午10:00使用Getdate（）填充的，它将不会出现在查询结果中。 - Sparky

我不知道，但它是以这种格式出现的：2008-01-02 00:00:00.000 - JsonStatham

@SQL_Surfer，我在这里问了一个非常类似的问题：https://dev59.com/fFjUa4cB1Zd3GeqPPTzd - Stuart Blackler

Answer 1

WHERE
    startdate <= '2011-12-01'
AND enddate   >= '2011-01-01'

（假设enddate中的值是项目活动的最后日期）

使用数字举例，搜索任何与100到200重叠的内容...

Start | End | Start <= 200 | End >= 100

 000  | 099 |  Yes         | No
 101  | 199 |  Yes         | Yes     (HIT!)
 201  | 299 |  No          | Yes
 000  | 150 |  Yes         | Yes     (HIT!)
 150  | 300 |  Yes         | Yes     (HIT!)
 000  | 300 |  Yes         | Yes     (HIT!)

这个逻辑绝对需要一个AND :)

关于您的查询...

您带括号的查询看起来像这样...

WHERE
  (
     startdate BETWEEN '01/04/2011' AND '01/12/2011'
  OR enddate   BETWEEN '01/04/2011' AND '01/12/2011'
  OR startdate <= '01/04/2011'
  )
  AND enddate >= '01/12/2011'

但是你的例子从来没有满足最后一个AND条件。尝试添加括号以更加明确...

WHERE
     (startdate BETWEEN '01/04/2011' AND '01/12/2011')
  OR (enddate   BETWEEN '01/04/2011' AND '01/12/2011')
  OR (startdate <= '01/04/2011' AND enddate >= '01/12/2011')

- MatBailie

---

有些项目始于2009年，预计在2013年结束。 - JsonStatham

我也会选择这个，因为SQL Server始终使用这种格式，并且它是国际标准。 - Asken

@SQL_Surfer - 我的第一个示例也适用于这些情况。有六种基本情况（全部在你的范围之前）（与你的范围开始重叠）（全部在你的范围内）（与范围结束重叠）（全部在你的范围之后）（与整个范围重叠）。这两行检查适用于这6种情况。 - MatBailie

Answer 2

在查询之前添加。

set dateformat dmy

也许还可以加上一些括号

WHERE 
(startdate BETWEEN '01/01/2011' AND '01/12/2011')
OR 
(enddate BETWEEN '01/01/2011' AND '01/12/2011')
OR 
(startdate <= '01/01/2011' AND enddate >= '01/12/2011')

- benni_mac_b

---

当我这样做时，我会得到完全不同数据的600多条记录。 - JsonStatham

抱歉，这是英国格式。01/01/2011 表示 2011 年 1 月 1 日，而 01/12/2011 表示 2011 年 12 月 1 日。 - JsonStatham

Answer 3

我的原始查询是有效的，但我连接的数据库与我的查询具有不同的日期格式。

- JsonStatham

Answer 4

假设startdate和enddate是日期字段，

尝试这个：

WITH Dates AS ( 
      SELECT [Date] = @StartDate
      UNION ALL SELECT [Date] = DATEADD(DAY, 1, [Date])
      FROM Dates WHERE [Date] <= DATEADD(DAY, -1, @EndDate)
) 
-- YOUR SELECT STATEMENT
-- YOUR FROM STATEMENT
CROSS APPLY Dates
WHERE [Date] BETWEEN startdate AND enddate 
-- The rest of your where statement here
OPTION(MAXRECURSION 0);

将您的开始日期声明为2011年01月01日，将您的结束日期声明为2011年01月12日

- Stuart Blackler

Answer 5

大家都在错误地比较startdate和enddate与字符串，而实际上你可以将字符串与列进行比较；以下是最简单的确定你想要的内容的方法：

where ( '01/04/2011' between startdate and enddate
          or  '01/12/2011' between startdate and enddate
         )

- Ben