从两个日期之间的日期范围中选择数据

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

我有一个名为Product_Sales的表，它包含这样的数据

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.

例如，我想选择2013-01-03到2013-01-09的销售数据。

18 个解决方案

#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:

您的SQL将如下所示:

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

#2

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'

#3

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.

你必须考虑到所有的可能性。From_Date或To_Date可以在您的日期范围内，或者记录日期可以覆盖整个范围。

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.

如果From_date或To_date中的一个在日期之间，或者From_date小于起始日期，而To_date大于结束日期;然后返回这一行。

#4

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

#5

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)

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

#7

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

#8

This query will help you:

此查询将帮助您:

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

#9

This working on SQL_Server_2008 R2

在SQL_Server_2008 R2上工作

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

#10

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)

我觉得使用“dd- mm -yyyy”格式最安全，因为无论服务器上的区域性设置如何，db服务器都能知道您想要什么。否则，您可能会在服务器上遇到问题，服务器的日期区域设置为yyyyy -dd-mm(无论出于什么原因)

Thus:

因此:

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

It's always worked well for me ;-)

这对我来说一直都很好;

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

#12

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

检查这个查询，我创建了这个查询，以检查在lap上的Check in日期是否具有任何预订日期

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

这将使正在覆盖的详细信息返回，以获取未覆盖的详细信息，然后从查询中删除“not”

#13

You can also try using following fragments:

你也可以尝试使用以下片段:

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

#14

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

您应该比较sql中的日期，就像比较数字值一样，

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

#15

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

这里有一个查询来查找在8月份运行的所有产品销售

• 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
• 排除任何在8月1日前结束的

Also adds a case statement to validate the query

还添加了一个case语句来验证查询。

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; 

#16

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.

其中“arvdate”和“dptrdate”是数据库的两个日期，“reservation”是表名。

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;

#17

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'

#18

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