Select data from a date range between two dates

Question

I have a Named Product_Sales table and it contains 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 succeed:

• enlist all acceptable parameters
• exclude all invalid parameters

Obviously, the second method is much simpler (only two cases versus four).

Your SQL will look like this:

 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 must embrace all possibilities. From_Date or To_Date can be between a date range or record dates can span the entire range.

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

Answer 3

Try a query to get dates between a 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

 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 6

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 7

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

Answer 8

Only my 2 cents, I believe that the "dd-MMM-yyyy" format is the most secure, since the db server will know what you want, regardless of the regional settings on the server. Otherwise, you might run into problems on a server with regional date settings like yyyy-dd-mm (for whatever reason)

In this way:

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

It always worked for me :-)

Answer 9

This work on SQL_Server_2008 R2

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

Answer 10

This query will help you:

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

Answer 11

 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 12

Mark this request, I created this request to check if the check date is checked on my lap with the booking dates

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

this will repeat the parts that overlap to get non-overlapping parts, and then remove “NOT” from the query

Answer 13

You can also try using the following snippets:

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

Answer 14

It's simple, use this query to find selected data from a date range between two dates

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

Answer 15

You have to compare dates in sql just like comparing numeric values,

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

Answer 16

Here is a query to find all product sales that were launched during August.

• Find Product_sales were active during August.
• Include everything that started before the end of August
• Exclude everything that ended before August 1.

Also adds a case statement to validate the request

 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

This code will work well:

Controller:

 $this->load->model("YourModelName"); $data ['query'] = $this->YourModelName->get_report(); 

Model:

  public function get_report() { $query = $this->db->query("SELECT * FROM reservation WHERE arvdate <= '2016-7-20' AND dptrdate >= '2016-10-25' "); return $query; } 

where "arvdate" and "dptrdate" are two dates in the database, and "reservation" is the name of the table.

View:

 <?php echo $query->num_rows(); ?> 

This code should return the number of rows. To return table data, use

 $query->rows(); return $row->table_column_name; 

Answer 18

 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 19

This covers all the conditions you are looking for.

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

Answer 20

it's 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'