0

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

  • It's not clear why you want both rows returned. Are you looking for rows that their range is "overlapping" with the range in the parameters? – ypercubeᵀᴹ May 17 '16 at 11:27
3

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' ;
  • this query show only one result if i search as Start_date = 2016-05-16 end_date= 2016-05-31 then actually i have to get both category. but using this query i did't get a result. i get empty return. @Frederico Almeida – ankit suthar May 17 '16 at 9:56
  • it depends on your data. If your field is datetime then sql server will convert the input date to datetime. For example: Input end date = 2016-05-31 then SQL will convert to datetime 2016-05-31 00:00:00. If your date in the table is 2016-05-31 01:00:00 then SQL will not get this result. Try converting your input date to datetime 2016-05-31 23:59:59. – Frederico Almeida May 18 '16 at 11:53
0

Most probably table row interval must be within interval defined by user input ('$start_date', '$end_date'). So

SELECT category 
FROM tablename t 
WHERE t.start_date BETWEEN '$start_date' AND '$end_date'  
    AND t.end_date BETWEEN '$start_date' AND '$end_date'; 
0

You have to use below query for your output.

SELECT category 
 FROM table_1 
   WHERE start_date BETWEEN '2016-05-16' AND '2016-05-31'
    OR end_date BETWEEN '2016-05-16' AND '2016-05-31'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.