Select data from date range between two dates

Question

I have a table Named Product_Sales and it holds data like this

Product_ID	Sold_by	Qty	From_date	To_date
3	12	7	2013-01-05	2013-01-07
6	22	14	2013-01-06	2013-01-10
8	11	9	2013-02-05	2013-02-11

Now what is the query if I want to select sales data between two dates from a date range?

For example, I want to select sales data from 2013-01-03 to 2013-01-09.

Answer 1

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)

Answer 2

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.

Answer 3

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'

Answer 4

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

Answer 5

This covers all conditions that you are looking for.

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

Answer 6

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'
)

Answer 7

Please try:

DECLARE @FrmDt DATETIME, @ToDt DATETIME
SELECT @FrmDt='2013-01-03', @ToDt='2013-01-09'

SELECT * 
FROM Product_sales 
WHERE (@FrmDt BETWEEN From_date AND To_date) OR 
    (@ToDt BETWEEN From_date AND To_date)

Answer 8

This is easy, use this query to find select data from date range between two dates

select * from tabblename WHERE (datecolumn BETWEEN '2018-04-01' AND '2018-04-5')

Answer 9

Just my 2 cents, I find using the "dd-MMM-yyyy" format safest as the db server will know what you want regardless of the regional settings on the server. Otherwise you could potentially run into issues on a server that has its date regional settings as yyyy-dd-mm (for whatsoever reason)

Thus:

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

It's always worked well for me ;-)

Answer 10

select * 
from table 
where
( (table.EndDate > '2013-01-05') and (table.StartDate < '2013-01-07' )  )

Answer 11

This query will help you:

select * 
from XXXX
where datepart(YYYY,create_date)>=2013 
and DATEPART(YYYY,create_date)<=2014

Answer 12

Check this query, i created this query to check whether the check in date over lap with any reservation dates

SELECT * FROM tbl_ReservedRooms
WHERE NOT ('@checkindate' NOT BETWEEN fromdate AND todate
  AND '@checkoutdate'  NOT BETWEEN fromdate AND todate)

this will retrun the details which are overlaping , to get the not overlaping details then remove the 'NOT' from the query

Answer 13

This working on SQL_Server_2008 R2

Select * 
from Product_sales
where From_date 
between '2013-01-03' and '2013-01-09'

Answer 14

SELECT NULL  
    FROM   HRMTable hm(NOLOCK)  
    WHERE  hm.EmployeeID = 123
        AND (  
                (  
                    CAST(@Fromdate AS date) BETWEEN CAST(hm.FromDate AS date)  
                        AND CAST(hm.ToDate AS date)  
                )  
                OR (  
                    CAST(@Todate AS date) BETWEEN CAST(hm.FromDate AS date)  
                        AND CAST(hm.ToDate AS date)  
                   )  
                ) 
         )

Answer 15

You can also try using following fragments:

select  * from  Product_sales 
where  From_date  >= '2013-01-03' and game_date  <= '2013-01-09'

Answer 16

Here is a query to find all product sales that were running during the month of August

• Find Product_sales there were active during the month of August
• Include anything that started before the end of August
• Exclude anything that ended before August 1st

Also adds a case statement to validate the query

SELECT start_date, 
       end_date, 
       CASE 
         WHEN start_date <= '2015-08-31' THEN 'true' 
         ELSE 'false' 
       END AS started_before_end_of_month, 
       CASE 
         WHEN NOT end_date <= '2015-08-01' THEN 'true' 
         ELSE 'false' 
       END AS did_not_end_before_begining_of_month 
FROM   product_sales 
WHERE  start_date <= '2015-08-31' 
       AND end_date >= '2015-08-01' 
ORDER  BY start_date; 

Answer 17

DECLARE @monthfrom int=null,
@yearfrom int=null,
@monthto int=null,
@yearto int=null,
@firstdate DATE=null,
@lastdate DATE=null

SELECT @firstdate=DATEADD(month,@monthfrom-1,DATEADD(year,@yearfrom-1900,0)) /*Setting First Date using From Month & Year*/
SELECT @lastdate= DATEADD(day,-1,DATEADD(month,@monthto,DATEADD(year,@yearto-1900,0)))/*Setting Last Date using From Month & Year*/

SELECT *  FROM tbl_Record
WHERE  (DATEADD(yy, Year - 1900, DATEADD(m, Month - 1, 1 - 1)) BETWEEN CONVERT(DATETIME, @firstdate, 102) AND 
CONVERT(DATETIME, @lastdate, 102))

Answer 18

You should compare dates in sql just like you compare number values,

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

Answer 19

this is easy, use this query to find what you want.

select * from Product_Sales where From_date<='2018-04-11' and To_date>='2018-04-11'