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.
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'
)
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)
select *
from table
where
( (table.EndDate > '2013-01-05') and (table.StartDate < '2013-01-07' ) )
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 ;-)
This working on SQL_Server_2008 R2
Select *
from Product_sales
where From_date
between '2013-01-03' and '2013-01-09'
This query will help you:
select *
from XXXX
where datepart(YYYY,create_date)>=2013
and DATEPART(YYYY,create_date)<=2014
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)
)
)
)
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
You can also try using following fragments:
select * from Product_sales
where From_date >= '2013-01-03' and game_date <= '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')
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'
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;
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 on database and 'reservation' is the table name.
View:
<?php
echo $query->num_rows();
?>
This code is to return number of rows. To return table data, then use
$query->rows();
return $row->table_column_name;
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))
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 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'
참고URL : https://stackoverflow.com/questions/14208958/select-data-from-date-range-between-two-dates