Select data from date range between two dates

Question

As you can see, there are two ways to get things done:

• enlist all acceptable options
• exclude all wrong options

Obviously, second way is much more simple (only two cases against four).

Your SQL will look like:

SELECT * FROM Product_sales 
WHERE NOT (From_date > @RangeTill OR To_date < @RangeFrom)

SELECT * from Product_sales where
(From_date BETWEEN '2013-01-03'AND '2013-01-09') OR 
(To_date BETWEEN '2013-01-03' AND '2013-01-09') OR 
(From_date <= '2013-01-03' AND To_date >= '2013-01-09')

You have to cover all possibilities. From_Date or To_Date could be between your date range or the record dates could cover the whole range.

If one of From_date or To_date is between the dates, or From_date is less than start date and To_date is greater than the end date; then this row should be returned.

Try following query to get dates between the range:

SELECT  *
FROM    Product_sales 
WHERE   From_date >= '2013-01-03' AND
        To_date   <= '2013-01-09'

SELECT * FROM Product_sales 
WHERE From_date between '2013-01-03'
AND '2013-01-09'

SELECT *
FROM Product_sales
WHERE (
From_date >= '2013-08-19'
AND To_date <= '2013-08-23'
)
OR (
To_date >= '2013-08-19'
AND From_date <= '2013-08-23'
)