CODEWITHSUNDEEP
mysqlsql
wobsoriano
wobsoriano

Reputation: 13442

How to select between/before/after dates in MySQL conditionally?

I have a table and would like to get rows that will fit a specific condition.

Say I have this row:

id start_date end_date
 3 2020-04-02 NULL

And in my web app UI, I have this calendar:

enter image description here

When you check the table fields, you'll see a start_date and an end_date. An end_date can be null meaning it has no end.

Getting back to the table, I want to get rows by passing startDates and endDates from UI. The startDate will be March 29 and endDate will be May 9 based on the calendar view.

Here's my current query:

SELECT * FROM slots WHERE start_date <= 2020-03-29 AND (end_date >= 2020-05-09 OR end_date IS NULL)

With the code above, I'm not getting any data. It's clearly because the start_date in the table is 2020-04-02 and my query checks for previous date and is equal to startDate from UI.

I also tried (greater than and equal to):

SELECT * FROM slots WHERE start_date >= 2020-03-29 AND (end_date >= 2020-05-09 OR end_date IS NULL)

And while I got the row, when I click the next button in the calendar and switch to May 2020, I'm not getting the row any more. I should get it because the end_date is null.

How can I achieve getting the row with id 3 even when changing months in the UI?

Upvotes: 0

Views: 706

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521429

You are trying to find records whose start and end dates overlap with a known range. This is the overlapping range problem, and you may try:

SELECT *
FROM slots
WHERE
    start_date < '2020-05-09' AND
    (end_date > '2020-03-29' OR end_date IS NULL);

This says that a valid range starts before the end date of your known range, and it ends after the start date of your known range, i.e. it lies inside that known range.

Note that date literals in MySQL take single quotes, so you need to fix that as well.

Upvotes: 4

Related Questions