SQL 从两个日期之间的日期范围中选择数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14208958/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select data from date range between two dates
提问by Ronjon
I have a table Named Product_Salesand 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-03to 2013-01-09.
例如，我想从2013-01-03to 中选择销售数据2013-01-09。
回答by Dmitry Lukichev
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)
回答by FallenAngel
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_Dateor To_Datecould be between your date range or the record dates could cover the whole range.
你必须涵盖所有的可能性。From_Date或To_Date可能介于您的日期范围之间，或者记录日期可能涵盖整个范围。
If one of From_dateor To_dateis between the dates, or From_dateis less than start date and To_dateis greater than the end date; then this row should be returned.
如果其中之一From_date或To_date在日期之间，或From_date小于开始日期且To_date大于结束日期；那么应该返回这一行。
回答by jkmurphy1
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'
回答by Berkay Turanc?
SELECT * FROM Product_sales
WHERE From_date between '2013-01-03'
AND '2013-01-09'
回答by Avinash
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')
回答by Kiran K
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'
)
回答by KDT
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)
只是我的 2 美分，我发现使用“dd-MMM-yyyy”格式最安全，因为无论服务器上的区域设置如何，数据库服务器都会知道您想要什么。否则，您可能会在日期区域设置为 yyyy-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 ;-)
它对我来说总是很好;-)
回答by I_Valchev
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'
回答by TechDo
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)
回答by Gestef
select *
from table
where
( (table.EndDate > '2013-01-05') and (table.StartDate < '2013-01-07' ) )
