Check if date is overlapping in SQL

I have a table tblBranchTimingEntry

+---------------+-------------------------+-------------------------+------------------+
| BranchEntryID |        fromDate         |         toDate          |     SundayIn     |
+---------------+-------------------------+-------------------------+------------------+
|            24 | 2015-01-01 00:00:00.000 | 2015-01-31 00:00:00.000 | 12:00:00.0000000 |
|            24 | 2015-02-01 00:00:00.000 | 2015-02-15 00:00:00.000 | 12:00:00.0000000 |
|            24 | 2015-03-01 00:00:00.000 | 2015-03-31 00:00:00.000 | 00:00:00.0000000 |
|            24 | 2014-01-01 00:00:00.000 | 2014-12-31 00:00:00.000 | 00:00:00.0000000 |
+---------------+-------------------------+-------------------------+------------------+

---

Requirement

I am giving input BranchEntryID, fromDate, toDate and I want to check if any date between fromDate and toDate over lap with the date ranges stored in tblBranchTimingEntry.

---

What I done so far

I have this query

SELECT 
        * 
    FROM
        [dbo].[tblBranchTimingEntry] 

    WHERE
        BranchEntryId = 24
    AND
        ('2015-01-14' BETWEEN fromDate AND toDate OR '2015-02-28' BETWEEN fromDate AND toDate)

This will check the overlap.

---

Problem

This will work only if the input date falls between the dates present in DB. This will fail in this example.

Suppose I'm giving fromdate and todate as '2015-02-16' and '2015-08-27', this query will not return anything. But there are dates overlapping between these date.

ANy Solution?

---

Try this logic:

SELECT te.* 
FROM [dbo].[tblBranchTimingEntry]  te
WHERE BranchEntryId = 24 AND
      '2015-01-14' < toDate AND
      '2015-02-28' > fromDate;

Depending on what you mean by "overlapping" that could be <= and/or >= .

The logic is: two date ranges overlap is the first starts before the second ends and the first ends after the second starts.

---

Try this:

SELECT 
        * 
FROM
        [dbo].[tblBranchTimingEntry]
WHERE
    BranchEntryId = 24
AND
    (('2015-01-14' < toDate AND
  '2015-01-14' > fromDate) or ('2015-02-28' > fromDate and '2015-02-28' < toDate) or ('2015-02-28' > toDate AND
  '2015-01-14' < fromDate))

That way you are checking if any of the dates is betwwen the fromDate and ToDate

链接地址: http://www.djcxy.com/p/64176.html

上一篇: 你如何改变用matplotlib绘制的图形的大小？

下一篇: 检查日期是否在SQL中重叠