我如何获得日期范围内的周末天数

问题描述：

我必须找到开始日期&结束日期之间的星期六和星期日总数。我如何获得日期范围内的周末天数

例子＃1：

StartDate = Getdate(), EndDate = GetDate() + 5  -- result should be 2.

例2：

StartDate = Getdate(), EndDate = GetDate() + 10  -- result should be 4.

任何人都可以提出请。

答

这

DECLARE @STARTDATE DATE='01/JAN/2014'  
DECLARE @ENDDATE DATE='01/MAR/2014' 

;WITH CTE as 
(
    SELECT CAST(@STARTDATE AS DATE) as [DAYS] 
    UNION ALL 
    SELECT DATEADD(DAY,1,[DAYS]) [DAYS] 
    FROM CTE 
    WHERE [DAYS] < CAST(@ENDDATE AS DATE) 
) 
SELECT DISTINCT COUNT([DAYS]) OVER(PARTITION BY DATENAME(WEEKDAY,[DAYS])) CNT, 
DATENAME(WEEKDAY,[DAYS]) WD 
FROM CTE 
WHERE DATENAME(WEEKDAY,[DAYS]) = 'SATURDAY' OR DATENAME(WEEKDAY,[DAYS]) = 'SUNDAY' 
ORDER BY DATENAME(WEEKDAY,[DAYS])

SQL FIDDLE

这里是你的结果

enter image description here

答

您可以使用日期部分http://msdn.microsoft.com/en-us/library/ms174420.aspx

一个例子;

WITH CTE as(
Select DATEPART(WeekDay,MyDate) as DP From Table Where Mydate > @StartDate and MyDate < @EndDate) 
Select Count(*) as CT,DP From CTE 
group by DP

星期六将7和周日将是1，所以你可以检查旁边的计数。

这不工作.... – sk7730 2014-12-05 07:38:32

答

试试这个：

declare @startdate datetime = getdate() 
declare @days int = 5 
declare @cal table(dt datetime) 

declare @counter int = 0 
while @counter < @days 
    begin 
    insert into @cal values (@startdate + @counter) --Ideally should be dateadd(dd,@counter,@startdate) 
    set @counter = @counter + 1 
end 

select count(*) from @cal 
where datename(dw,dt) = 'Saturday' or datename(dw,dt) = 'Sunday' 
--Ideally should be 
--where datename(dw,dt) = 1 or datename(dw,dt) = 7

Demo

我们正在做的是从你开始建立天的列表结束日期，然后计算这些日期的周末。一个表变量用来存储这个列表。那应该注意

2点：

理论上，应该使用dateadd函数来执行日期时间计算，而不是+操作。
虽然我为了清晰起见使用了datename，但datepart会更好，因为它会给出数值，而datename会给出与语言相关的值。

答

试试这个：

DECLARE @V_StartDate DATETIME = GETDATE(), @V_EndDate DATETIME = GETDATE() + 5; 

WITH showDateCTE(DateCol) 
AS 
(
SELECT DateCol = @V_StartDate 
UNION ALL 
SELECT DATEADD(DAY, 1, DateCol) 
FROM showDateCTE 
WHERE DateCol < @V_EndDate - 1 
) 
SELECT COUNT(1) weekEndCount 
FROM showDateCTE 
WHERE DATENAME(dw, CONVERT(DATE, DateCol)) IN ('Saturday', 'Sunday');

答

declare @startdate datetime 
    declare @enddate datetime 
    declare @weekendCnt int 
    set @startdate = getdate() 
    set @enddate = getdate()+8 
    set @weekendCnt = 0 

    while @startdate < @enddate 
    begin 
    PRINT @startdate 
    if(datename(dw, @startdate) in('Saturday','Sunday')) 
     begin 
      set @weekendCnt = @weekendCnt + 1   
     end 
     set @startdate = @startdate +1 
    end 
    print @weekendCnt

以上解决方案工作正常。 – sk7730 2014-12-05 10:03:34

答

今天有同样的问题。我到了这里。

如果您不想使用递归（CTE）或while。你可以使用数学加例时：

DECLARE @StartDate AS DATE 
DECLARE @EndDate AS DATE 
SET @StartDate = Getdate() 
SET @EndDate = GetDate() + 11 

SELECT 
    -- Full WE (*2 to get num of days Sa and So)    
    (((DATEDIFF(d,@StartDate,@EndDate)+1)/7)*2) 
    + 
    -- WE-Days in between; given that Saturday = 7 AND Sunday = 1 
    -- what if startdate is sunday And you have remaining Days; you will always only get one WE-day 
    CASE WHEN DATEPART(dw,@StartDate) = 1 AND (DATEDIFF(d,@StartDate,@EndDate)+1)%7 > 0 THEN 1 
     -- If you have remaining days (Modulo 7 > 0) and the sum of number of starting day and remaining days is 8 (+1 for startingdate) then you have + 1 WE-day (its a saturday) 
     ELSE CASE WHEN (DATEDIFF(d,@StartDate,@EndDate)+1)%7 > 0 AND (((DATEDIFF(d,@StartDate,@EndDate)+1)%7) + DATEPART(dw,@StartDate)) = 8 THEN 1 
      -- If the remaining days + the number of the weekday is are greater then 8 (+1 for startingdate) you have 2 days of the weekend in between. 
      ELSE CASE WHEN (DATEDIFF(d,@StartDate,@EndDate)+1)%7 > 0 AND (((DATEDIFF(d,@StartDate,@EndDate)+1)%7) + DATEPART(dw,@StartDate)) > 8 THEN 2 
      -- you have no WE-days in between! Either because of the fact that you have a number that is divisable by 7 or because the remaining days are between 2 (Tuesday) and 6 (Friday) 
      ELSE 0 
      END 
     END 
    END AS TotalWEDays

我希望它通过评论变得清晰。让我知道它是否有帮助。

我如何获得日期范围内的周末天数

相关推荐