Comparing Date Ranges

Comparing date ranges

This is a classical problem, and it's actually easier if you reverse the logic.

Let me give you an example.

I'll post one period of time here, and all the different variations of other periods that overlap in some way.

           |-------------------|          compare to this one
               |---------|                contained within
           |----------|                   contained within, equal start
                   |-----------|          contained within, equal end
           |-------------------|          contained within, equal start+end
     |------------|                       not fully contained, overlaps start
                   |---------------|      not fully contained, overlaps end
     |-------------------------|          overlaps start, bigger
           |-----------------------|      overlaps end, bigger
     |------------------------------|     overlaps entire period

on the other hand, let me post all those that doesn't overlap:

           |-------------------|          compare to this one
     |---|                                ends before
                                 |---|    starts after

So if you simple reduce the comparison to:

starts after end
ends before start

then you'll find all those that doesn't overlap, and then you'll find all the non-matching periods.

For your final NOT IN LIST example, you can see that it matches those two rules.

You will need to decide wether the following periods are IN or OUTSIDE your ranges:

           |-------------|
   |-------|                       equal end with start of comparison period
                         |-----|   equal start with end of comparison period

If your table has columns called range_end and range_start, here's some simple SQL to retrieve all the matching rows:

SELECT *
FROM periods
WHERE NOT (range_start > @check_period_end
           OR range_end < @check_period_start)

Note the NOT in there. Since the two simple rules finds all the non-matching rows, a simple NOT will reverse it to say: if it's not one of the non-matching rows, it has to be one of the matching ones.

Applying simple reversal logic here to get rid of the NOT and you'll end up with:

SELECT *
FROM periods
WHERE range_start <= @check_period_end
      AND range_end >= @check_period_start

Compare data between two date ranges in SQL Server

The first thing I would do is to create common table expressions for your date ranges, each having two columns: a column with the data value, and a column with row number value. This way, It's going to be very easy to join by n-th value.

So here is my suggested solution:

First, Create and populate sample table (Please save us this step in your future questions)

DECLARE @data As Table
(
    [date] DATE,
    [value] INT
);

INSERT INTO @data
VALUES
('2018-01-01', 3),
('2018-01-02', 5),
('2018-01-03', 8),
('2018-01-04', 6),
('2018-02-04', 4),
('2018-02-05', 2),
('2018-02-06', 7),
('2018-02-07', 0);

Now, I've changed your @currentStartDateTime from 2018-02-04 to 2018-02-03,
to make sure I also return rows that are not in the table (Please make sure your sample data covers all requierments)

DECLARE @currentStartDateTime  datetime   = '2018-02-03 00:00:00',
        @currentEndDateTime    datetime   = '2018-02-07 23:59:59',
        @previousStartDateTime datetime   = '2018-01-01 00:00:00',
        @previousEndDateTime   datetime   = '2018-01-03 23:59:59'

Now, my solution seems quite cumbersome because I wanted to show all steps in details.
You might be able to simplify it.

First, calculate the max date difference in days.

Then, create a numbers cte from 1 to that difference + 1.

Then, create calendar ctes for each range,

Then a final cte to do a full join between the ranges,

and a select from that final cte left joined twice to the data table.

-- This allows us to use the smallest possible tally cte.
DECLARE @MaxDateDiff int;
SELECT @MaxDateDiff = MAX(d)
FROM (
    VALUES  (DATEDIFF(DAY, @currentStartDateTime, @currentEndDateTime)), 
            (DATEDIFF(DAY, @previousStartDateTime, @previousEndDateTime))
    ) v(d) -- I like table value constructors :-)

;WITH Tally AS 
(
    SELECT TOP (@MaxDateDiff + 1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) As Number
    FROM sys.objects a
    -- if your database is very small (number of tables, procedures ect'), 
    -- you might want to unremark the next row
    -- CROSS JOIN sys.objects b
),
CurrentRange AS  
(
    SELECT DATEADD(DAY, Number-1, @currentStartDateTime) As [Date], Number
    FROM Tally
    -- we need the where clause in case the other range is bigger...
    WHERE DATEADD(DAY, Number-1, @currentStartDateTime) <= @currentEndDateTime
), 
PreviousRange AS 
(
    SELECT DATEADD(DAY, Number-1, @previousStartDateTime) As [Date], Number
    FROM Tally
    WHERE DATEADD(DAY, Number-1, @previousStartDateTime) <= @previousEndDateTime
), 
BothRanges AS 
(        
    SELECT C.Date As CurDate, P.Date As PreDate
    FROM CurrentRange As C
    FULL JOIN PreviousRange As P ON C.Number =  P.Number
)

SELECT CurDate, ISNULL(c.Value, 0) as CurValue, PreDate, ISNULL(p.Value, 0) as PreValue
FROM BothRanges
LEFT JOIN @data AS c ON CurDate = c.[Date]
LEFT JOIN @data AS p ON PreDate = p.[Date]

Results: (Remember that @currentStartDateTime is different than the one on the question)

CurDate                 CurValue    PreDate                 PreValue
03.02.2018 00:00:00     0           01.01.2018 00:00:00     3
04.02.2018 00:00:00     4           02.01.2018 00:00:00     5
05.02.2018 00:00:00     2           03.01.2018 00:00:00     8
06.02.2018 00:00:00     7           NULL                    0
07.02.2018 00:00:00     0           NULL                    0

You can see a live demo on rextester.

Determine Whether Two Date Ranges Overlap

(StartA <= EndB) and (EndA >= StartB)

Proof:

Let ConditionA Mean that DateRange A Completely After DateRange B

_                        |---- DateRange A ------|
|---Date Range B -----|                          _

(True if StartA > EndB)

Let ConditionB Mean that DateRange A is Completely Before DateRange B

|---- DateRange A -----|                        _ 
_                          |---Date Range B ----|

(True if EndA < StartB)

Then Overlap exists if Neither A Nor B is true -

(If one range is neither completely after the other,

nor completely before the other,
then they must overlap.)

Now one of De Morgan's laws says that:

Not (A Or B) <=> Not A And Not B

Which translates to: (StartA <= EndB) and (EndA >= StartB)

NOTE: This includes conditions where the edges overlap exactly. If you wish to exclude that,

change the >= operators to >, and <= to <

NOTE2. Thanks to @Baodad, see this blog, the actual overlap is least of:

{ endA-startA, endA - startB, endB-startA, endB - startB }

(StartA <= EndB) and (EndA >= StartB)
(StartA <= EndB) and (StartB <= EndA)

NOTE3. Thanks to @tomosius, a shorter version reads:

DateRangesOverlap = max(start1, start2) < min(end1, end2)

This is actually a syntactical shortcut for what is a longer implementation, which includes extra checks to verify that the start dates are on or before the endDates. Deriving this from above:

If start and end dates can be out of order, i.e., if it is possible that startA > endA or startB > endB, then you also have to check that they are in order, so that means you have to add two additional validity rules:

(StartA <= EndB) and (StartB <= EndA) and (StartA <= EndA) and (StartB <= EndB)
or:

(StartA <= EndB) and (StartA <= EndA) and (StartB <= EndA) and (StartB <= EndB)
or,

(StartA <= Min(EndA, EndB) and (StartB <= Min(EndA, EndB))
or:

(Max(StartA, StartB) <= Min(EndA, EndB)

But to implement Min() and Max(), you have to code, (using C ternary for terseness),:

(StartA > StartB? Start A: StartB) <= (EndA < EndB? EndA: EndB)

NOTE4. Thanks to Carl for noticing this, but another answer shows an equivalent mathematical expression for this logical expression. Because the product of any two real numbers with opposite sign is negative and with the same sign it is positive, if you convert the datetimes to fractional numbers (and most DBMSs internally use numbers to represent datetimes), the above logical expression can also be evaluated using the following mathematical expression:

(EndA  - StartA) * (StartB - EndB) <= 0

How to compare two date ranges in Android?

tl;dr

LocalDateRange a = LocalDateRange.ofClosed(
    LocalDate.parse( "2021-07-01" ) ,
    LocalDate.parse( "2021-07-08" )
)
.abuts(                                            // Or .overlaps or .isConnected
    LocalDateRange b = LocalDateRange.ofClosed(
        LocalDate.parse( "2021-07-08" ) ,
        LocalDate.parse( "2021-07-10" )
    )
)

Avoid legacy date-time classes

using Calendar object

Never use Calendar or Date. These terrible classes were supplanted years ago by the java.time classes defined in JSR 310.

Date ranges

if the first two given dates are 2021-7-1 and 2021-7-8 and the second two given dates are 2021-7-8 and 2021-7-10, it should return false because the date ranges of the first two given dates and the second two given dates are overwritten at 2021-7-8.

You want to compare two date ranges.

`LocalDate`

In modern Java we use LocalDate to represent a date without a time-of-day and without the context of a time zone or offset-from-UTC.

`org.threeten.extra.LocalDateRange`

To work with date ranges (a pair of LocalDate objects), I suggest you add the ThreeTen-Extra library to your project, free-of-cost and open-source. This library includes the LocalDateRange class, just what you need.

Half-open spans

You should know that in date-time handling, it usually makes sense to define a span of time as Half-Open, where the beginning is inclusive while the ending is exclusive. So a full day starts at the first moment of the day and runs up to, but does not include, the first moment of the next day.

In your case, your first range ends on the 8th, and the second range begins on the 8th. By Half-Open rules, these two ranges would abut rather than overlap.

LocalDateRange x = LocalDateRange.of(
        LocalDate.parse( "2021-07-01" ) ,
        LocalDate.parse( "2021-07-08" )
);
LocalDateRange y = LocalDateRange.of(
        LocalDate.parse( "2021-07-08" ) ,
        LocalDate.parse( "2021-07-10" )
);

boolean abuts = x.abuts( y );
boolean overlaps = x.overlaps( y );

When run.

abuts = true
overlaps = false

I believe making consistent use of Half-Open makes your code easier to read and reason about, and is less likely to have mistakes from an oversight.

Fully-closed spans

However, if you insist on the Fully-Closed approach, the LocalDateRange class lets you work that way. Use LocalDateRange.ofClosed rather than LocalDateRange.of.

LocalDateRange a = LocalDateRange.ofClosed(
        LocalDate.parse( "2021-07-01" ) ,
        LocalDate.parse( "2021-07-08" )
);
LocalDateRange b = LocalDateRange.ofClosed(
        LocalDate.parse( "2021-07-08" ) ,
        LocalDate.parse( "2021-07-10" )
);

boolean abuts = a.abuts( b );
boolean overlaps = a.overlaps( b );

When run.

abuts = false
overlaps = true

Using the Fully-Closed approach, your two ranges overlap rather than abut, the opposite of what we saw above in previous example code.

Overlaps or abuts

Be aware of the isConnected method, the equivalent to (overlaps(other) || abuts(other)). The LocalDateRange class offers other comparison methods too.

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes. Hibernate 5 & JPA 2.2 support java.time.

Where to obtain the java.time classes?

Java SE 8, Java SE 9, Java SE 10, Java SE 11, and later - Part of the standard Java API with a bundled implementation.
- Java 9 brought some minor features and fixes.
Java SE 6 and Java SE 7
- Most of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
Android
- Later versions of Android (26+) bundle implementations of the java.time classes.
- For earlier Android (<26), the process of API desugaring brings a subset of the java.time functionality not originally built into Android.
  - If the desugaring does not offer what you need, the ThreeTenABP project adapts ThreeTen-Backport (mentioned above) to Android. See How to use ThreeTenABP….

Table of which java.time library to use with which version of Java or Android

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.

Compare date Range by Month (Integer) and Year (Integer)

A simple approach uses arithmetics:

where year * 100 + month 
    between year(startdate) * 100 + month(startdate)
        and year(enddate)   * 100 + month(enddate)

However this probably isn't the most efficient method. In general, you want to avoid applying functions on the column you filter on. A better alternative woul be to convert the year/month parameter to a date - unfortunately you did not tag your database, and date functions are highly vendor-specific, so it is not really possible to suggest.

If you don't want between:

where year * 100 + month >= year(startdate) * 100 + month(startdate)
  and year * 100 + month <= year(enddate)   * 100 + month(enddate)

Compare date ranges in ISO format

Ciao, you could use function isBetween provided by moment in this way:

// interval comes from API
let dateAPIFrom = moment().toISOString();
let dateAPITo = moment().add(2, "days").toISOString();

// user date interval
let convertedDateFrom = moment(setSelectedDayRange.from).toISOString();
let convertedDateTo = moment(setSelectedDayRange.to).toISOString();

if (
  moment(convertedDateFrom)
    .subtract(1, "month")
    .isBetween(dateAPIFrom, dateAPITo) &&
  moment(convertedDateTo)
    .subtract(1, "month")
    .isBetween(dateAPIFrom, dateAPITo)
) {

  //The user input date-range fall completely within the date-range of the API

} else if (
  moment(convertedDateFrom)
    .subtract(1, "month")
    .isBetween(dateAPIFrom, dateAPITo) ||
  moment(convertedDateTo)
    .subtract(1, "month")
    .isBetween(dateAPIFrom, dateAPITo)
) {

  //or at least one of the date values could fall or overlap within the date-range from the API.
 
}

.subtract(1, "month") because moment({day: 19, month: 8, year: 2020}).toISOString() returns always month + 1.

Here your codesandbox modified.

Comparing date with a date range in two tables

You will have to do that with two nested loops. One for all entries in lt_tab1 and an inner loop which scans lt_tab2 for any entries which match the current entry in tab1. You can then use a flag to indicate whether or not a matching entry was found.

DATA lv_found_match TYPE abap_bool.

LOOP AT lt_tab1 ASSIGNING <fs_tab1>.

   " find out if there is a matching entry in table 2 for this entry
   lv_found_match = abap_false.
   LOOP AT lt_itab2 ASSIGNING <fs_tab2>.
     IF <fs_tab2>-startdate <= <fs_tab1>-workdate AND
        <fs_tab2>-enddate >= <fs_tab1>-workdate.
       lv_found_match = abap_true.
       EXIT. " the LOOP AT
     ENDIF.
   ENDLOOP.

   IF lv_found_match = abap_true.
       " there is a match
   ELSE.
       " there isn't a match
   ENDIF.

ENDLOOP.

Comparing Date Ranges