使用sql server获取不包括周末的日期 [英] get DATEDIFF excluding weekends using sql server

问题描述

我正在使用此查询来获取时间.

I am using this query to get time taken.

SELECT DATEDIFF(dd, ActualStartDate, ActualCompletionDate) AS TimeTaken
FROM TableName

现在我想排除周末，只包括周一至周五作为计算天数.

Now I want to exclude weekends and only include Mon-Fri as days counted.

推荐答案

下面的示例查询，这里是我如何解决它的一些细节.

Example query below, here are some details on how I solved it.

使用 DATEDIFF(WK, ...) 会给我们两个日期之间的周数.SQL Server 将此评估为周数之间的差异，而不是基于天数.这是完美的，因为我们可以使用它来确定日期之间经过了多少个周末.

Using DATEDIFF(WK, ...) will give us the number of weeks between the 2 dates. SQL Server evaluates this as a difference between week numbers rather than based on the number of days. This is perfect, since we can use this to determine how many weekends passed between the dates.

因此，我们可以将该值乘以 2 以获得发生的周末天数，然后从 DATEDIFF(dd, ...) 中减去该值以获得工作日数.

So we can multiple that value by 2 to get the number of weekend days that occurred and subtract that from the DATEDIFF(dd, ...) to get the number of weekdays.

不过，当开始日期或结束日期落在星期日时，这不会 100% 正确运行.所以我在计算的最后添加了一些 case 逻辑来处理这些实例.

This doesn't behave 100% correctly when the start or end date falls on Sunday, though. So I added in some case logic at the end of the calculation to handle those instances.

您可能还需要考虑 DATEDIFF 是否应该完全包含在内.例如9/10 和 9/11 的区别是 1 天还是 2 天?如果是后者，您需要在最终产品中加 1.

You may also want to consider whether or not the DATEDIFF should be fully inclusive. e.g. Is the difference between 9/10 and 9/11 1 day or 2 days? If the latter, you'll want to add 1 to the final product.

declare @d1 datetime, @d2 datetime
select @d1 = '9/9/2011',  @d2 = '9/18/2011'

select datediff(dd, @d1, @d2) - (datediff(wk, @d1, @d2) * 2) -
       case when datepart(dw, @d1) = 1 then 1 else 0 end +
      case when datepart(dw, @d2) = 1 then 1 else 0 end

这篇关于使用sql server获取不包括周末的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！