CODEWITHSUNDEEP
mysqlsqldatetime
Jazerix
Jazerix

Reputation: 4801

MySQL Best Approach to get all days between two date values

I need to find all dates that are within two dates. This would normally be done with

BETWEEN

, but I have a StartDate and en EndDate column. This means that the date actually stretches over some days.

So a record could have values like this:

id, status, startDate, endDate

How can I find all rows, that are within those dates, provided I give the query a two dates.

Upvotes: 0

Views: 379

Answers (1)

Decent Dabbler
Decent Dabbler

Reputation: 22783

To get records where the complete record period lies in the requested period:

SELECT * 
FROM yourtable
WHERE startDate >= smallestDate AND 
      endDate <= largestDate;

To get records where the record period intersects the requested period:

SELECT * 
FROM yourtable
WHERE startDate <= largestDate AND 
      endDate >= smallestDate;

And, for completeness, to get the records where the start of the records period lies in the requested period, but you don't care about the end of the record period:

SELECT * 
FROM yourtable
WHERE startDate BETWEEN smallestDate AND largestDate

And, vice versa, for the end date of the record period:

SELECT * 
FROM yourtable
WHERE endDate BETWEEN smallestDate AND largestDate

(All examples assume startDate is always smaller than the endDate, of course.)

Upvotes: 2

Related Questions