Checking If a Given Date Fits Between a Range of Dates

Checking if a given date fits between a range of dates

With SQL Server it's actually as simple as:

SELECT startDate, endDate
FROM YourTable
WHERE '2012-10-25' between startDate and endDate

Query to check if a given range of date doesn't fits between multiple range of dates

Here is a simple way to check for date overlap in an insert query

insert into mytable(startDate, endDate)
select i.startDate, i.endDate
from (select '2019-12-23' startDate, '2019-12-30' endDate) i
where not exists (
    select 1 
    from mytable t 
    where t.startDate <= i.endDate and t.endDate >= i.startDate
)

The date range to insert is declared in the subquery aliased i. If any record in the table overlaps with that range, the insert is skipped, else it happens.

Demo on DB Fiddle:

-- set up
CREATE TABLE mytable(
   id int auto_increment primary key
   ,startDate DATE  NOT NULL 
  ,endDate   DATE  NOT NULL
);
INSERT INTO mytable(startDate,endDate) VALUES ('2019-12-10','2019-12-15');
INSERT INTO mytable(startDate,endDate) VALUES ('2019-12-16','2019-12-22');
INSERT INTO mytable(startDate,endDate) VALUES ('2019-12-29','2019-01-05');
INSERT INTO mytable(startDate,endDate) VALUES ('2020-01-20','2020-01-25');

-- initial table content
select * from mytable order by startDate

id | startDate  | endDate   
-: | :--------- | :---------
 1 | 2019-12-10 | 2019-12-15
 2 | 2019-12-16 | 2019-12-22
 3 | 2019-12-29 | 2019-01-05
 4 | 2020-01-20 | 2020-01-25

-- this range does not overlap
insert into mytable(startDate, endDate)
select i.startDate, i.endDate
from (select '2019-12-23' startDate, '2019-12-30' endDate) i
where not exists (
    select 1 
    from mytable t 
    where t.startDate <= i.endDate and t.endDate >= i.startDate
)

-- confirm it was inserted
select * from mytable order by id

id | startDate  | endDate   
-: | :--------- | :---------
 1 | 2019-12-10 | 2019-12-15
 2 | 2019-12-16 | 2019-12-22
 3 | 2019-12-29 | 2019-01-05
 4 | 2020-01-20 | 2020-01-25
 5 | 2019-12-23 | 2019-12-30

-- this range overlaps
insert into mytable(startDate, endDate)
select i.startDate, i.endDate
from (select '2019-12-23' startDate, '2019-12-28' endDate) i
where not exists (
    select 1 
    from mytable t 
    where t.startDate <= i.endDate and t.endDate >= i.startDate
)

-- it was not inserted
select * from mytable order by id

id | startDate  | endDate   
-: | :--------- | :---------
 1 | 2019-12-10 | 2019-12-15
 2 | 2019-12-16 | 2019-12-22
 3 | 2019-12-29 | 2019-01-05
 4 | 2020-01-20 | 2020-01-25
 5 | 2019-12-23 | 2019-12-30

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) ? StartA : StartB) <= ((EndA < EndB) ? EndA : EndB)

How do I check if a date is within a certain range?

boolean isWithinRange(Date testDate) {
   return !(testDate.before(startDate) || testDate.after(endDate));
}

Doesn't seem that awkward to me. Note that I wrote it that way instead of

return testDate.after(startDate) && testDate.before(endDate);

so it would work even if testDate was exactly equal to one of the end cases.

Check if one date is between two dates

Date.parse supports the format mm/dd/yyyy not dd/mm/yyyy. For the latter, either use a library like moment.js or do something as shown below

var dateFrom = "02/05/2013";
var dateTo = "02/09/2013";
var dateCheck = "02/07/2013";

var d1 = dateFrom.split("/");
var d2 = dateTo.split("/");
var c = dateCheck.split("/");

var from = new Date(d1[2], parseInt(d1[1])-1, d1[0]);  // -1 because months are from 0 to 11
var to   = new Date(d2[2], parseInt(d2[1])-1, d2[0]);
var check = new Date(c[2], parseInt(c[1])-1, c[0]);

console.log(check > from && check < to)

Checking if a date falls between a range of dates

When humans specify date-only ranges, they tend to be fully closed ranges. How many days are there from 2014-01-01 to 2014-01-02? TWO.

But when time is added to the mix, either in a time-only range, or a date+time range, then these ranges are usually half-open ranges. How many hours from 1:00 to 2:00? ONE.

Now the problem is that JavaScript's Date object is really a date+time object. (It's misnamed, IMHO). When you don't specify a time, it sets the time to the first moment of the day - which is usually (but not always) midnight.

So you might think that you would do this:

var a = new Date(2014, 0, 1);
var b = new Date(2014, 0, 2);
var inRange = dt >= a && dt <= b;

But that doesn't account for the time of day that's being secretly added by the Date object. A value in the middle of the day on Jan 2 would not be included in this range.

So to compensate, you have to add a day, then use a half-open interval:

var a = new Date(2014, 0, 1);
var b = new Date(2014, 0, 2);

var c = new Date(b.getTime());   // clone the date to not muck the original value
c.setDate(c.getDate()+1);        // add a day
var inRange = dt >= a && dt < c; // use a half-open interval

Using a shortened value like 23:59:59.999 might get you by, but it's not a good approach in general. Subtracting the start from the end of an interval should give you it's exact duration - not one millisecond less.

count row if date falls within date range for all dates in series in R

Here's a base R method:

date = seq(min(df$start_date), max(df$end_date))
count = sapply(date, \(x) sum(x >= df$start_date & x <= df$end_date))
data.frame(date, count)
#    date count
# 1     1     2
# 2     2     4
# 3     3     5
# 4     4     6
# 5     5     7
# 6     6     6
# 7     7     5
# 8     8     4
# 9     9     3
# 10   10     2
# 11   11     1

determine if date range falls between another date range - sql

How can you get daterange1 = 0, since it's a range, i.e. 2 values?

The proper test for overlapping dates is

CASE WHEN @range1start <= @range2end
      and @range2start <= @range1end THEN 1 ELSE 0 END

If you mean that daterange2 must fall ENTIRELY within daterange1, then

CASE WHEN @range1start <= @range2start
      and @range2end <= @range1end THEN 1 ELSE 0 END

Checking between date ranges javascript

You can simply check if a date is in range with getTime()

JSBin

const isDateInRage = (startDate, endDate) => (dateToCheck) => {  return dateToCheck >= startDate && dateToCheck <= endDate}
const isInRangeOne = isDateInRage('2016-01-01', '2016-04-30')
console.log('inRange', isInRangeOne('2016-01-02'))console.log('outtOfRange', isInRangeOne('2016-07-02'))

PHP check if date between two dates

Edit: use <= or >= to count today's date.

This is the right answer for your code. Just use the strtotime() php function.

$paymentDate = date('Y-m-d');
$paymentDate=date('Y-m-d', strtotime($paymentDate));
//echo $paymentDate; // echos today! 
$contractDateBegin = date('Y-m-d', strtotime("01/01/2001"));
$contractDateEnd = date('Y-m-d', strtotime("01/01/2012"));
    
if (($paymentDate >= $contractDateBegin) && ($paymentDate <= $contractDateEnd)){
    echo "is between";
}else{
    echo "NO GO!";  
}

---

Related Topics