CODEWITHSUNDEEP
mysqlsqldatetime
Jazerix
Jazerix

Reputation: 4801

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 )
)

This returns me two results: 1 and 3

what am I doing wrong?

Upvotes: 8

Views: 16492

Answers (4)

RenegadeWizard
RenegadeWizard

Reputation: 11

This is just the where clause. Given InputStartDate and InputEndDate are given by user input, and DataStartDate and DataEndDate are the datetime values in the table:

where ((DataEndDate > InputStartDate) and (DataStartDate < InputEndDate))

Upvotes: 1

Paul Ishak
Paul Ishak

Reputation: 1093

This is an old thread, but use BETWEEN. This is an excerpt from my timeclock, pls modify to your needs...

$qs = "SELECT COUNT(*) AS `count` FROM `timeclock` WHERE `userid` = :userid 
                AND (
                    (`timein` BETWEEN :timein AND :timeout OR `timeout` BETWEEN :timein AND :timeout )
                    OR
                    (:timein BETWEEN `timein` AND `timeout` OR :timeout BETWEEN `timein` AND `timeout`)
                    );";

Upvotes: 4

Hussein Akar
Hussein Akar

Reputation: 425

You can cover all date overlapping cases even also when toDate in database can possibly be null as follows:

SELECT * FROM `tableName` t
WHERE t.`startDate` <= $toDate
AND (t.`endDate` IS NULL OR t.`endDate` >= $startDate);

This will return all records that overlaps with the new start/end dates in anyway.

Upvotes: 0

ffflabs
ffflabs

Reputation: 17481

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)

Upvotes: 48

Related Questions