检索 MySQL 中特定日期范围内发生的所有记录 [英] Retrieve all records that occur within specific date range in MySQL

问题描述

我有一个包含多个合约的表​​格，每个合约都有一个开始日期和一个结束日期，如下所示:

I have a table that contains several contracts, and each contract has a start date and an end date, like this:

|  ID   |   Contract Name   |  Start Date  |  End Date  |
|-------|-------------------|--------------|------------|
|   1   |  Joe Bloggs       |  2012-01-01  | 2012-02-05 |
|   2   |  John Smiths      |  2012-02-01  | 2012-02-20 |
|   3   |  Johnny Briggs    |  2012-03-01  | 2012-03-20 |

我想做的是构建一个查询，该查询将检索在特定时间段内有效的合同.所以如果我有 2012-02-10 的开始日期和 2012-03-21 的结束日期，我应该显示以下合同:

What I am trying to do is build a query that will retrieve contracts that were active between a specific time period. So if I had the start date of 2012-02-10 and an end date of 2012-03-21 I should have the following contracts displayed:

|  ID   |   Contract Name   |  Start Date  |  End Date  |
|-------|-------------------|--------------|------------|
|   2   |  John Smiths      |  2012-02-01  | 2012-02-20 |
|   3   |  Johnny Briggs    |  2012-03-01  | 2012-03-20 |

我的问题是我不知道如何构建查询来做到这一点.这是我到目前为止所得到的:

My problem though is that I don't know how to build the query to do this. This is what I've got so far:

SELECT *
FROM contracts c
WHERE c.startdate BETWEEN '2012-02-10'
    AND '2012-03-21'
    AND c.enddate BETWEEN '2012-02-10'
    AND '2012-03-21'

但这不起作用，没有检索到任何记录.我做错了什么?

This doesn't work though, no records are retrieved. What am I doing wrong?

推荐答案

呃，时间是线性的吧?

SELECT * 
FROM contracts 
WHERE end_date >= '2012-02-10' 
AND start_date <= '2012-03-21';

让我举例说明...

    A-------------B
<------->
       <------>
           <----------->
<---------------------->

在上述所有情况下，开始日期都小于 B.结束日期大于 A.

In all cases above, the start date is less than B. The end date is greater than A.

这篇关于检索 MySQL 中特定日期范围内发生的所有记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！