CODEWITHSUNDEEP
mysqlsql
malli p
malli p

Reputation: 108

How to select rows between two different columns with two different input values on same table on mysql

I have a table name as Orders. Orders table have two columns names are start_date,end_date

S.No     start_date   end_date
 1       2016-04-01   2016-04-08
 2       2016-04-28   2016-05-29
 3       2016-05-01   2016-05-39

Now I want records between start date of 2016-04-01 and end date of 2016-04-30.

Please help me on this

Upvotes: 2

Views: 905

Answers (5)

ScottieB
ScottieB

Reputation: 4052

If you want only orders that ran from 4/1 - 4/30 then:

SELECT *
FROM
   orders
WHERE
   start_date = '2016-4-1'
   AND end_date = '2016-4-30'

If you want orders that either started on 4/1 OR ended on 4/30 then:

SELECT *
FROM
   orders
WHERE
   start_date = '2016-4-1'
   OR end_date = '2016-4-30'

Edit after OP comment

SELECT *
FROM
    orders
WHERE
    start_date between '2016-4-1' AND '2016-4-30'
    OR end_date between '2016-4-1' AND '2016-4-30'

Upvotes: 0

Arth
Arth

Reputation: 13110

Assuming you want records that overlap the range:

SELECT *
  FROM orders
 WHERE start_date <= :end_range
   AND end_date   >= :start_range

In your case:

SELECT *
  FROM orders
 WHERE start_date <= '2016-04-30'
   AND end_date   >= '2016-04-01'

This works as below:

           <---RANGE-->
     <-->                                     X end_date   < :start_range
R      <---->                                 Returned
O             <---->                          Returned
W         <--------------->                   Returned
S                   <---->                    Returned
                            <-->              X start_date > :end_range

Upvotes: 3

mituw16
mituw16

Reputation: 5250

Is this what you're looking for? 

select * 
from Orders 
where 
    (start_date >= '2016-04-01' and start_date < '2016-05-01');

Edit Update to reflect comments from OP.

SQL Fiddle

Upvotes: 0

Jon Chapman
Jon Chapman

Reputation: 13

Do you want: select * from orders where start_date between '04/01/2016' AND '04/30/2016' AND end_date between '04/01/2016' AND '04/30/2016'

Upvotes: 1

D&#39;Arcy Rittich
D&#39;Arcy Rittich

Reputation: 171411

Assuming start_date is always <= end_date, you may get better results with the query below, as it gives more specificity on the start_date filter:

select * 
from Orders 
where start_date between '2016-04-01' and '2016-04-30'
    and end_date between '2016-04-01' and '2016-04-30'

Upvotes: 1

Related Questions