MySQL检查两个日期范围是否与输入重叠 [英] MySQL check if two date range overlap with input

问题描述

我需要检查数据库中是否有两个日期和另外两个日期.

I need to check if two dates over lap with another two dates in my database.

我的数据库看起来像这样

My database looks like this

+----+--------------+------------+------------+
| id | code         | StartDate  | EndDate    |
+----+--------------+------------+------------+
|  1 | KUVX-21-40   | 2013-10-23 | 2013-11-22 |
|  2 | UEXA286-1273 | 2013-10-30 | 2013-11-29 |
|  3 | UAJFAU-2817  | 2013-10-21 | 2013-11-20 |
|  4 | KUVX-21-40   | 2013-10-30 | 2013-11-29 |
+----+--------------+------------+------------+

在我的查询中，我指定范围:开始日期和结束日期 让我们对其进行如下分配:

In my query i specify the scope: A start date and an enddate Lets asign them as follows:

ScopeStartDate = "2013-10-1"
ScopeEndDate = "2013-11-26"

由于所有记录都超过了时间跨度，因此上述应归还我所有记录.

Above should return me all of the records, since the all overlapse the timespan.

但是我无法使查询正常工作:/

However I cannot get a query working :/

我没有运气尝试过以下查询:

I've tried the following query with no luck:

WHERE
(
    (StartDate < ScopeStartDate AND StartDate > ScopeStartDate)
    OR
    (StartDate > ScopeStartDate  AND EndDate < ScopeEndDate )
)

这会给我两个结果: 1和3

This returns me two results: 1 and 3

我在做什么错了?

推荐答案

我相信以下条件会匹配所有可能重叠的情况.

I believe the following condition matches every possible overlapping case.

WHERE
(
    (ScopeStartDate <= EndDate AND ScopeEndDate >= StartDate)

)

除非您声明不合逻辑的时间跨度(例如，在开始之前结束的时间跨度)

except if you declare illogic timespans (for example, those which end before starting)

这篇关于MySQL检查两个日期范围是否与输入重叠的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！