CODEWITHSUNDEEP
mysql
user11981274
user11981274

Reputation:

Check if a date falls between two dates in MySQL database?

I have a MySQL database with city, class (class 1, 2, 3, 4, 5), start date and end date. I have a web application (PHP) that I need to check to see if the class matches in a date range.

So for example, demo table:

City    Class      StartDate      EndDate
LA      1          2019-09-13    2019-10-29
SF      2          2019-09-13    2019-10-29

I need to query this database to check if the city, class matches the date falls in-between those dates.

Is there a way I can do this with MySQL and then pass it back to PHP? How would I go about doing this?

I am able to query MySQL this way to return the row:

SELECT * FROM blackout where '2019-09-13' BETWEEN start_date AND end_date AND city="SF" AND class="2";

But I am looking for more of a binary true or false return.

Upvotes: 2

Views: 3287

Answers (2)

Maulik Patel
Maulik Patel

Reputation: 63

try this query for your solution

SELECT IF(COUNT(*) > 0, 'true', 'false') AS result FROM blackout WHERE (date(start_date) >= "2019-09-03" AND date(end_date) <= "2019-09-03") AND city="SF" AND class="2";

hope this may help you. let me know if you face any issue.

Upvotes: 0

nbk
nbk

Reputation: 49373

The result of

      SELECT EXISTS(SELECT *
       FROM blackout where '2019-09-13' BETWEEN StartDate AND EndDate 
       AND city="SF" AND class="2");

is 1

And for 

      SELECT EXISTS(SELECT *
       FROM blackout where '2017-09-13' BETWEEN StartDate AND EndDate 
       AND city="SF" AND class="2");

You get an 0

Upvotes: 2

Related Questions