Comparing date ranges

Question

In MySQL, If I have a list of date ranges (range-start and range-end). e.g.

10/06/1983 to 14/06/1983
15/07/1983 to 16/07/1983
18/07/1983 to 18/07/1983

And I want to check if another date range contains ANY of the ranges already in the list, how would I do that?

e.g.

06/06/1983 to 18/06/1983 = IN LIST
10/06/1983 to 11/06/1983 = IN LIST
14/07/1983 to 14/07/1983 = NOT IN LIST

Answer 1

In your expected results you say

06/06/1983 to 18/06/1983 = IN LIST

However, this period does not contain nor is contained by any of the periods in your table (not list!) of periods. It does, however, overlap the period 10/06/1983 to 14/06/1983.

You may find the Snodgrass book (http://www.cs.arizona.edu/people/rts/tdbbook.pdf) useful: it pre-dates mysql but the concept of time hasn't changed ;-)

Answer 2

Taking your example range of 06/06/1983 to 18/06/1983 and assuming you have columns called start and end for your ranges, you could use a clause like this

where ('1983-06-06' <= end) and ('1983-06-18' >= start)

i.e. check the start of your test range is before the end of the database range, and that the end of your test range is after or on the start of the database range.

Answer 3

Try This on MS SQL

---

WITH date_range (calc_date) AS (
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, [ending date]) - DATEDIFF(DAY, [start date], [ending date]), 0)
UNION ALL SELECT DATEADD(DAY, 1, calc_date)
FROM date_range 
WHERE DATEADD(DAY, 1, calc_date) <= [ending date])
SELECT  P.[fieldstartdate], P.[fieldenddate]
FROM date_range R JOIN [yourBaseTable] P on Convert(date, R.calc_date) BETWEEN convert(date, P.[fieldstartdate]) and convert(date, P.[fieldenddate]) 
GROUP BY  P.[fieldstartdate],  P.[fieldenddate];

Answer 4

CREATE FUNCTION overlap_date(s DATE, e DATE, a DATE, b DATE)
RETURNS BOOLEAN DETERMINISTIC
RETURN s BETWEEN a AND b or e BETWEEN a and b or  a BETWEEN s and e;

Answer 5

Look into the following example. It will helpful for you.

    SELECT  DISTINCT RelatedTo,CAST(NotificationContent as nvarchar(max)) as NotificationContent,
                ID,
                Url,
                NotificationPrefix,
                NotificationDate
                FROM NotificationMaster as nfm
                inner join NotificationSettingsSubscriptionLog as nfl on nfm.NotificationDate between nfl.LastSubscribedDate and isnull(nfl.LastUnSubscribedDate,GETDATE())
  where ID not in(SELECT NotificationID from removednotificationsmaster where Userid=@userid) and  nfl.UserId = @userid and nfl.RelatedSettingColumn = RelatedTo

Answer 6

Another method by using BETWEEN sql statement

Periods included :

SELECT *
FROM periods
WHERE @check_period_start BETWEEN range_start AND range_end
  AND @check_period_end BETWEEN range_start AND range_end

Periods excluded :

SELECT *
FROM periods
WHERE (@check_period_start NOT BETWEEN range_start AND range_end
  OR @check_period_end NOT BETWEEN range_start AND range_end)