SQL Select – How to Select Two Records Containing Dates with Two Input Dates

select

Simply I have Two Input box

  1. Start_date
  2. End_date

This is my database table

| id | category | start_date | end_date   |
| 1  | gold     | 2016-05-24 | 2016-05-31 |
| 2  | silver   | 2016-05-16 | 2016-05-23 |

Now When I insert date like below:

start_date = 2016-05-25
end_date = 2016-05-27

I want to find out category between imputed date in input box.

I use this select query: 

SELECT category 
FROM tablename
WHERE '$start_date','$end_date' BETWEEN start_date AND end_date;

But I don't get any result. I know that if you use single date after where I get result but I want to search with two dates

Best Answer

Simple answer:

If you want both date columns to be between the input dates:

SELECT category 
FROM tablename 
WHERE 'start_date' BETWEEN start_date AND end_date
  AND 'end_date' BETWEEN start_date AND end_date ;

Or if you want any of the date columns to be between the input dates:

SELECT category 
FROM tablename 
WHERE 'start_date' BETWEEN start_date AND end_date
   OR 'end_date' BETWEEN start_date AND end_date ;

Or if you want any of the ranges that are "overlapping with the range of the input dates
(seems the most likely, based on the wanted result):

SELECT category 
FROM tablename 
WHERE 'start_date' <= end_date
  AND start_date <= 'end_date' ;
Related Question