SQL日期范围介于周与周之间

Question

我们正在为员工假期制作一个日期范围选择器。

例如，我将使用2016年1月作为示例，我们的系统从周日到周六

如果员工从1月14日到20日休假，他们应该在10-16和17-23的日期范围内

我无法想象如何编写一个sql查询将其限制在这两个日期内。

我所拥有的是：

DECLARE @WeekRangeStart DATETIME ='2016/01/10';
DECLARE @WeekRangeEnd DATETIME = '2016/01/16';

SELECT [ID], 
       [EmpName], 
       [EmpType] 
FROM Vacations
WHERE VacationStartDate >= @WeekRangeStart OR VacationEndDate >= @WeekRangeStart 

--OUTPUT
--ALL DAYS BEFORE THIS WOULD BE TRUE...
--1/14/2016 >= 1/10/2016 TRUE
--1/20/2016 >= 1/10/2016 TRUE
-- NEXT WEEK 
--1/14/2016 >= 1/17/2016 FALSE
--1/20/2016 >= 1/17/2016 TRUE
-- NEXT WEEK 
--1/14/2016 >= 1/24/2016 FALSE
--1/20/2016 >= 1/24/2016 FALSE
--ALL DAYS AFTER THIS DAY WOULD BE FALSE...

但这只适用于已经过去的事情，但如果我在3月份预订一天，我总是会在日程表上显示，因为我的开始日期会比今天更大。我应该如何将其限制在该范围内？

Answer 1

DECLARE @WeekRangeStart DATETIME ='2016/01/10';
DECLARE @WeekRangeEnd DATETIME = '2016/01/16';

SELECT [ID], 
   [EmpName], 
   [EmpType] 
FROM Vacations
WHERE VacationStartDate between @WeekRangeStart and @WeekRangeEnd
     or VacationEndDate Between @WeekRangeStart and @WeekRangeEnd

Answer 2

这可能会很有用。使用递归CTE，我得到了假期的所有日期。然后根据提供的周范围返回它所跨越的周。

DECLARE @WeekRangeStart DATETIME ='2016/01/10';
DECLARE @WeekRangeEnd DATETIME = '2016/01/16';
DECLARE @VacationStartDate DATETIME = '2016-01-14'
DECLARE @VacationEndDate DATETIME = '2016-01-20'


;WITH cte AS
  (
    SELECT @VacationStartDate AS Dates
    UNION ALL
    SELECT DATEADD(dd, 1, dates)
    FROM cte
    WHERE dates < @VacationEndDate
  ) 

SELECT DISTINCT
 DATEADD(dd, -(DATEPART(dw, dates)-1), dates) AS WeekStartDate,
 DATEADD(dd, 7-(DATEPART(dw, dates)), dates) AS WeekEndDate
FROM cte
WHERE dates BETWEEN @WeekRangeStart AND @WeekRangeEnd