CODEWITHSUNDEEP
phpmysql
Topher
Topher

Reputation: 183

Select projects active during a date range

I'm building a PHP project tool and I am trying to work out how to select (or exclude) projects during a certain date range. Each project has a "start_date" and "end_date" in a mysql table, but a BETWEEN seems to only look at the value itself, not the dates that occur between my written values.

For example, if I am trying to select projects that would be active between July 16 and July 20, how can I ensure that a row in my table with the start and end dates of July 1 to July 20 would get returned?

Here is a sample of the code that doesn't work:

$sql = "SELECT *
FROM 
    projects
WHERE
    proj_start_date BETWEEN '2018-07-16' AND '2018-07-20'
AND
    proj_end_date BETWEEN '2018-07-16' AND '2018-07-20'
GROUP BY
    proj_start_date";

The trouble with the able is that it wouldn't grab something that starts before and ends after the range.

GOT IT! The correct answer below got me on the right track. Here is the code that did the trick:

$sql = "
SELECT *
FROM 
    projects
WHERE
    proj_start_date <= '2018-07-20'
AND
    proj_end_date >= '2018-07-16'
GROUP BY
    proj_start_date";

Upvotes: 0

Views: 273

Answers (1)

Webdeveloper_Jelle
Webdeveloper_Jelle

Reputation: 2856

If your column types are datetime or date. Your query should be:

SELECT * FROM `tablename` WHERE start_date >= '2018-07-01' AND end_date <= '2018-07-20'

Or with between:

SELECT * FROM `tablename` WHERE start_date BETWEEN '2018-07-01' AND '2018-07-20'

Upvotes: 2

Related Questions