CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Sharon
Sharon

Reputation: 767

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?

Upvotes: 3

Views: 1437

Answers (2)

kleinohad
kleinohad

Reputation: 5912

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269543

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.

Upvotes: 3

Related Questions