SQL 检查给定日期是否在一系列日期之间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13161044/
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
Checking if a given date fits between a range of dates
提问by oshirowanen
If I have 2 date columns in a table, startDateand endDate. How do I return rows where a given date fits between those 2 dates? For example:
如果我在表中有 2 个日期列，startDate并且endDate. 如何返回给定日期在这两个日期之间的行？例如：
If the given date is 2012-10-25
如果给定的日期是 2012-10-25
It should return the following rows
它应该返回以下行
startDate - endDate
2012-10-25 - 2012-10-25
2011-09-10 - 2013-11-15
2012-10-20 - 2012-10-25
2012-10-23 - 2012-10-28
2012-09-14 - 2012-10-28
from the following rows:
从以下行：
startDate - endDate
2012-10-25 - 2012-10-25
2011-09-10 - 2013-11-15
2012-01-11 - 2012-10-11
2012-10-20 - 2012-10-25
2012-04-15 - 2012-04-16
2012-05-20 - 2012-05-25
2012-12-01 - 2012-12-10
2012-10-23 - 2012-10-28
2012-09-14 - 2012-10-28
2012-11-13 - 2012-12-15
Is this possible with sql?
这可以用sql吗？
I am using sql server 2008.
我正在使用 sql server 2008。
回答by Chad
With SQL Server it's actually as simple as:
使用 SQL Server，它实际上很简单：
SELECT startDate, endDate
FROM YourTable
WHERE '2012-10-25' between startDate and endDate
回答by Azodious
回答by bummi
for other lappingcheck the following might be interesting
对于其他 lappingcheck，以下内容可能很有趣
Select * from sted where [dbo].[F_LappingDays](Startdate,EndDate,'20121025','20121025')=1
CREATE Function [dbo].[F_LappingDays](@Von1 datetime,@bis1 Datetime,@von2 Datetime,@bis2 Datetime) Returns int as
/*
20110531 Thomas Wassermann
Terminüberschneidungen finden
*/
begin
Declare @Result int
Select @Result = 0
if (@Von1>=@Von2) and (@bis1<=@Bis2)
begin
Select @Result=Cast(@Bis1 - @von1 + 1 as Int)
end
else if (@Von1<=@Von2) and (@bis1 > @Von2) and (@bis1<=@Bis2)
begin
Select @Result=Cast(@Bis1 - @von2 + 1 as Int)
end
else if (@Von1>=@Von2) and (@von1<=@bis2) and (@bis1>@Bis2)
begin
Select @Result=Cast(@Bis2 - @von1 + 1 as Int)
end
else if (@Von1<@Von2) and (@bis1>@Bis2)
begin
Select @Result=Cast(@Bis2 - @von2 + 1 as Int)
end
Return @Result
end
