[sql] Select data from date range between two dates

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.

This question is related to sql date range

The answer is


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


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)

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


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

This working on SQL_Server_2008 R2

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

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

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'

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

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'

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

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


This query will help you:

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

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

interval intersection description

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

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; 

You can also try using following fragments:

select  * from  Product_sales 
where  From_date  >= '2013-01-03' and game_date  <= '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'
)

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to date

How do I format {{$timestamp}} as MM/DD/YYYY in Postman? iOS Swift - Get the Current Local Time and Date Timestamp Typescript Date Type? how to convert current date to YYYY-MM-DD format with angular 2 SQL Server date format yyyymmdd Date to milliseconds and back to date in Swift Check if date is a valid one change the date format in laravel view page Moment js get first and last day of current month How can I convert a date into an integer?

Examples related to range

How does String substring work in Swift Creating an Array from a Range in VBA How to create range in Swift? Why is "1000000000000000 in range(1000000000000001)" so fast in Python 3? How to find integer array size in java How does one make random number between range for arc4random_uniform()? Skip over a value in the range function in python Excel Define a range based on a cell value How can I generate a random number in a certain range? Subscript out of range error in this Excel VBA script