CODEWITHSUNDEEP
phpmysql
kvimalkr55
kvimalkr55

Reputation: 33

get data between start date and end date

Get data between start date and end date

I am getting data but my requirement is little bit different pls check the below database

I have two text boxs 

1) start date
2) end date

Database is like this

table name->purchase_order

   sno       start_date          end_date
     1       2017/08/01          2017/12/01
     2       2017/08/01          2017/11/30
     3       2017/09/01          2017/09/30
     4       2017/09/01          2017/10/30
     5       2017/10/01          2017/11/30

I am trying like this

select  * 
from    purchase_order 
where   start_date>= '2017/09/01' and start_date<= '2017/09/01' 
        OR end_date>= '2017/09/01' and end_date<= '2017/09/01'

Output i am getting 

 sno     start_date     end_date
 3       2017/09/01          2017/09/30
 4       2017/09/01          2017/10/30

What i require

if i select between this 2017/09/01 - 2017/09/30 i want out put like this {in id "1" in between 8th month to 12th month there is 9th month is there so it also has to come}

 sno       start_date          end_date
 1       2017/08/01          2017/12/01
 2       2017/08/01          2017/11/30
 3       2017/09/01          2017/09/30
 4       2017/09/01          2017/10/30

thanks

Upvotes: 1

Views: 1985

Answers (2)

Eli
Eli

Reputation: 2608

You would not want to have 4 clauses as you have in your question, you would want to have just 2.
The logic should be as follows:

  1. the start date from the input in the application is later or equal to the start date in the start column in your table
  2. the end date from the input in the application is earlier or equal to the end date in the end column in your

application.

Based on this logic, you should be able to use the following query:

SELECT 
    * 
FROM    purchase_order 
WHERE   start_date<= '2017/09/01'  -- start date input
AND     end_date<= '2017/09/01'  -- end date input

One may complain that I have a hole in my logic due to the fact that I don't check to see that the end date is later than the start date, though you would want to do that at the application level, hence it is not relevant at the database level where this question was asked.

Upvotes: 0

aynber
aynber

Reputation: 23001

Instead of checking that the column values are between your dates, you want to see if your dates are between the column values. Using BETWEEN makes the logic look a little cleaner:

select * from purchase_order where  '2017/09/01' BETWEEN start_date AND end_date

If you select 2017/09/01 - 2017/09/30, then do this:

select * from purchase_order where  '2017/09/01' BETWEEN start_date AND end_date AND '2017/09/30' BETWEEN start_date AND end_date

AND will make sure both dates are between start_date and end_date, OR will make sure at least one of the dates is between the columns.

Upvotes: 1

Related Questions