5

I'm looking for an extended answer to the question asked here:

Determine Whether Two Date Ranges Overlap

where any of the dates in either date range can be null. I've come up with the following solution, but I'm not sure if it can be simplified further.

(StartA == NULL || StartA <= EndB) &&
(EndA == NULL || EndA >= StartB) &&
(StartB == NULL || StartB <= EndA) &&
(EndB == NULL || EndB >= StartA)

Assuming:

DateTime ranges of StartA to EndA and StartB to EndB

EDIT: Sorry I quickly threw the above logic together, which seems to fail when either range's start and end dates are NULL. See David's solution below for a better & well-explained approach.

Community
  • 1
  • 1
Josh
  • 75
  • 2
  • 7
  • you'll have an error if StartA is null, because the compare operator doesn't works with null. Convert all the or Gate{Logic} to and{Logic} then reorganize the statements by putting all compare to null at the start so the short circuit and{Logic} will work. – Waleed A.K. Jul 08 '10 at 05:05

4 Answers4

14

This case can be handled by a slight generalization of Charles Bretana's excellent answer to that question.

Let CondA Mean DateRange A Completely After DateRange B (True if StartA > EndB) Let CondB Mean DateRange A Completely Before DateRange B (True if EndA < StartB)

In this case, assuming you want a null date to represent "no starting/ending bound," the conditions are modified. For CondA, for instance, in order for DateRange A to be completely after DateRange B, DateRange A must have a defined starting time, DateRange B must have a defined ending time, and the starting time of A must be after the ending time of B:

CondA := (StartA != null) && (EndB != null) && (StartA > EndB)

CondB is the same with A and B switched:

CondB := (StartB != null) && (EndA != null) && (StartB > EndA)

Continuing,

Then Overlap exists if Neither A Nor B is true

Overlap := !(CondA || CondB)

and

Now deMorgan's law, I think it is, says that

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

Overlap == !CondA && !CondB
        == ![(StartA != null) && (EndB != null) && (StartA > EndB)] &&
           ![(StartB != null) && (EndA != null) && (StartB > EndA)]
        == [(StartA == null) || (EndB == null) || (StartA <= EndB)] &&
           [(StartB == null) || (EndA == null) || (StartB <= EndA)]

I think this is actually a bit more robust than the solution you developed, because if EndB == NULL but StartA is not null, your first condition will wind up comparing StartA <= NULL. In most languages I'm familiar with, that's an error condition.

Community
  • 1
  • 1
David Z
  • 128,184
  • 27
  • 255
  • 279
  • Thanks for the thorough explanation, David! Yeah, I realized my initial solution falls apart pretty quickly soon after I posted it. – Josh Jul 07 '10 at 15:58
  • Nice Approach as deMorgan's law, But you can not implement the second part as programming statement, because if any of startA or EndB as example are equal to null, the the compare operator will fail. Try to keep just the And {&&} operator, because it will work as a short circuit in most new compilers. This is Correct as low and as programming. ![(StartA != null) && (EndB != null) && (StartA > EndB)] && ![(StartB != null) && (EndA != null) && (StartB > EndA)] – Waleed A.K. Jul 08 '10 at 04:55
  • 1
    @Waleed: If you're talking about my final two lines of code, that certainly can be implemented as a statement. If `startA` or `endB` is equal to `null`, that will get caught by the explicit tests for that condition and the `<=` comparison will never be made. `||` also uses short-circuit evaluation. (Most compilers would probably optimize using DeMorgan's law so that the last two lines and the preceding two lines would generate the same compiled code.) – David Z Jul 08 '10 at 06:54
1

Without considering nulls, answer is

(StartA <= EndB) and (EndA >= StartB) (see this for detailed explanation)

considering nulls for start and end dates,
Using C Ternary operator syntax:
(StartA != null? StartA: EndB <= EndB != null? EndB: StartA) && (EndA != null? EndA: StartB >= StartB != null? StartB: EndA)

Or C# 4.x style null operators:

(StartA??EndB <= EndB??StartA) && (EndA??StartB >= StartB??EndA)

or in SQL:

(Coalesce(StartA, EndB) <= Coalesce(EndB, StartA)) And (Coalesce(EndA, StartB ) <= Coalesce(StartB , EndA))

Explanation:
consider the non-null answer:
(StartA <= EndB) and (EndA >= StartB)

Now, consider that StartA is null, indicating that date range A has existed since beginning of time (BOT). In that case, DateRangeB can never be before DateRangeA. So first condition, (StartA(BOT) <= EndB) will ALWAYS be true, no matter what EndB is. So change this expression so that instead of comparing null with EndB, when StartA is null, compare EndB with itself No matter what EndB is, the expression EndB <= EndB will be true. (We could create variables to represent BOT and EOT, but this is easier).

Do the same for other three input variables.

Charles Bretana
  • 143,358
  • 22
  • 150
  • 216
-1

That is probably as 'simple' as you can get it, although I haven't actually proven it.

It probably isn't worth it to simplify further, since that block ends up being about 8 operations in the worst case (4 on average thanks to short-circuit evaluation).

Adam Shiemke
  • 3,734
  • 2
  • 22
  • 23
-1

All answers are based if the condition is true. I'would like to add some note here.

1- The DateTime variable type is a struct and you can not set it to null unless that you are using nullable type like "DateTime?"

2- To find the overlap range follow the following steps

DateTime? StartOverLap = null,EndOverLap = null;
            if (StartA != null && StartB != null)
            {
                StartOverLap = StartA > StartB ? StartA : StartB;
            }
            else if (StartA == null && StartB != null)
            {
                StartOverLap = StartB;
            }
            else if (StartA != null && StartB == null)
            {
                StartOverLap = StartA;
            }
            if (EndA != null && EndB != null)
            {
                EndOverLap = EndA < EndB ? EndA : EndB;
            }
            else if (EndA == null && EndB != null)
            {
                EndOverLap = EndB;
            }
            else if (EndA != null && EndB == null)
            {
                EndOverLap = EndA;
            }
            if (StartOverLap != null && EndOverLap == null)
            {
                if (EndOverLap < StartOverLap)
                {
                    StartOverLap = null;
                    EndOverLap = null;
                }
            }
Waleed A.K.
  • 1,596
  • 13
  • 13