T-SQL。如何检索给定了2个日期范围的行，并将其与两个日期列进行比较。

Question

以下是给定表数据：

CREATE TABLE #tbl_period (
    period_no INT,
    period_from SMALLDATETIME,
    period_to SMALLDATETIME
)

INSERT INTO #tbl_period (period_no, period_from, period_to)
VALUES (1, '2004-11-01 00:00:00', '2005-10-31 00:00:00')

INSERT INTO #tbl_period (period_no, period_from, period_to)
VALUES (2, '2005-11-01 00:00:00', '2006-10-31 00:00:00')

INSERT INTO #tbl_period (period_no, period_from, period_to)
VALUES (3, '2006-11-01 00:00:00', '2007-10-31 00:00:00')

INSERT INTO #tbl_period (period_no, period_from, period_to)
VALUES (4, '2007-11-01 00:00:00', '2008-10-31 00:00:00')

INSERT INTO #tbl_period (period_no, period_from, period_to)
VALUES (5, '2008-11-01 00:00:00', '2009-10-31 00:00:00')

INSERT INTO #tbl_period (period_no, period_from, period_to)
VALUES (6, '2009-11-01 00:00:00', '2010-10-31 00:00:00')

INSERT INTO #tbl_period (period_no, period_from, period_to)
VALUES (7, '2010-11-01 00:00:00', '2011-10-31 00:00:00')

SELECT * FROM #tbl_period

DROP TABLE #tbl_period

现在，我们的目标是检索具有2个给定日期的period_no，这些日期将与period_from和period_to列进行比较。

Example1:
date1 = '2010-11-01 00:00:00'
date2 = '2011-10-31 00:00:00'

这将返回7作为period_no

Example2: 
date1 = '2005-11-01 00:00:00'
date2 = '2007-10-31 00:00:00'

它不应返回period_no，因为给定的日期范围不在行中的period_from和period_to列的范围内。不允许日期重叠。

规则是，如果给定的两个日期匹配，或者在一行中的period_from和period_to列的范围内，则检索period_no。

如何在T-SQL中进行此查询？

提前感谢！

Answer 1

我想这就是你想要的：

SELECT 
    *
FROM 
    #tbl_period 
WHERE 
    @date1 >= period_from 
    and
    @date2 <= period_to

测试：

declare @date1 datetime = '2010-11-01 00:00:00' 
declare @date2 datetime = '2011-10-31 00:00:00' 

SELECT * FROM #tbl_period WHERE @date1 >= period_from and @date2 <= period_to

返回

period_no    period_from          period_to
7            2010-11-01 00:00:00  2011-10-31 00:00:00

和

set @date1 = '2005-11-01 00:00:00' 
set @date2 = '2007-10-31 00:00:00' 

SELECT * FROM #tbl_period WHERE @date1 >= period_from and @date2 <= period_to

返回

period_no   period_from period_to