如何查找两个日期的日期差异并排除星期日

Question

DECLARE @totaldays INT, 
@weekenddays INT, 
@startDate datetime = '2023/01/01', 
@endDate datetime = '2023/01/08'
SET @totaldays = DATEDIFF(DAY, @startDate, @endDate) +1 
SET @weekenddays = ((DATEDIFF(WEEK, @startDate, @endDate) * 2) + 
CASE 
WHEN DATEPART(WEEKDAY, @startDate) = 1 
THEN 1 
ELSE 0 
END + 
CASE 
WHEN DATEPART(WEEKDAY, @endDate) = 6 
THEN 1 
ELSE 0 
END)
SELECT (@totaldays - @weekenddays) AS Days

我用这个代码来查找2个日期的datediff，不包括'Sunday'。

结果是5，但它应该是6。

如果@startDate datetime = '2023/01/01'， @endDate datetime = '2023/01/07'结果= 6，答案为正确。

如何使它成为正确的查询?

Answer 1

这可能是有可能使这个CASE WHEN结构正确运行，但在我看来，这是真的不好读。我会这样做:

DECLARE @startdate datetime
DECLARE @enddate datetime
DECLARE @count int
SET @count = 0
SET @startdate = '2023/01/01'
SET @enddate = '2023/01/08'
WHILE @startdate <= @enddate
BEGIN
IF DATEPART(WEEKDAY,@startdate) <> 1
SET @count = @count + 1
SET @startdate = DATEADD(d,1,@startdate)
END
SELECT @count AS Days;

这个简单的想法是使用一个计数器，只有当这一天不是星期天时，它才会加1。

试试这里:db<>fiddle

Answer 2

你可以试试这个吗:

我对你的查询做了一些小调整

DECLARE @totaldays INT,
@weekenddays INT, 
@startDate datetime = '2023/01/01', 
@endDate datetime = '2023/01/08'
SET @totaldays = DATEDIFF(DAY, @startDate, @endDate) +1 
SET @weekenddays = (select datediff(day,@startDate,@endDate) /7 +
case   
when datepart(WEEKDAY,@startDate) = 1 then 1
when datepart(WEEKDAY,@startDate) > datepart(Weekday,@endDate) then 1
else 0
end)
SELECT (@totaldays - @weekenddays) AS Days

你可以从这里试试:https://dbfiddle.uk/vBYoOWW6