如何使用SQL Server增加某些日期（不包括周末）？ [英] How do I add to some date excluding weekends using SQL Server?

问题描述

例如：

@dtBegin = '2012-06-29' 

@input = 20

我希望输出为'2012-07-27' 。

推荐答案

我必须在项目中解决相同的问题。戈登·利奥诺夫（Gordon Lionoff）的解决方案使我步入正轨，但并非总是能产生正确的结果。我还必须考虑一个周末开始的日期。即在周六或周日增加1个工作日，则应为星期一。这是处理营业日计算的最常用方法。

I had to tackle the same problem in my project. Gordon Lionoff's solution got me on the right track but did not always produce the right result. I also have to take in account dates that start in a weekend. I.e. adding 1 business day to a saturday or sunday should result in a monday. This is the most common approach to handling business day calculation.

我已经基于Gordon Linoff的函数和帕特里克·麦当劳出色的C＃等价物

I've created my own solution based on Gordon Linoff's function and Patrick McDonald's excellent C# equivalent

注意：仅当DATEFIRST设置为默认值7 。如果使用其他DATEFIRST值，则必须更改 1 ， 7 和 （1,7,8,9,10）位。

NOTE: My solution only works if DATEFIRST is set to the default value of 7. If you use a different DATEFIRST value, you will have to change the 1, 7 and (1,7,8,9,10) bits.

我的解决方案包含两个功能。处理边缘情况的外部函数和执行实际计算的内部函数。这两个函数都是表值函数，因此它们实际上将被扩展到实现查询中并通过查询优化器进行馈送。

My solution consists of two functions. An "outer" function that handles edge cases and an "inner" function that performs the actual calculation. Both functions are table-valued functions so they will actually be expanded into the implementing query and fed through the query optimizer.

CREATE FUNCTION [dbo].[UTL_DateAddWorkingDays]
(   
    @date datetime,
    @days int
)
RETURNS TABLE AS RETURN 
(
    SELECT 
        CASE 
            WHEN @days = 0 THEN @date
            WHEN DATEPART(dw, @date) = 1 THEN (SELECT Date FROM [dbo].[UTL_DateAddWorkingDays_Inner](DATEADD(d, 1, @date), @days - 1))
            WHEN DATEPART(dw, @date) = 7 THEN (SELECT Date FROM [dbo].[UTL_DateAddWorkingDays_Inner](DATEADD(d, 2, @date), @days - 1))
            ELSE (SELECT Date FROM [dbo].[UTL_DateAddWorkingDays_Inner](@date, @days))
        END AS Date
)

如您所见，外部函数处理：

As you can see, the "outer" function handles:

• 不添加任何天数时，返回原始日期。这将保持星期六和星期日的日期不变。这将消耗1天。


• 在星期六添加天数时，从星期一开始计数。这将耗时1天。


• 在所有其他情况下，执行通常的计算

_

CREATE FUNCTION [dbo].[UTL_DateAddWorkingDays_Inner]
(   
    @date datetime,
    @days int
)
RETURNS TABLE AS RETURN 
(
    SELECT 
        DATEADD(d
        , (@days / 5) * 7 
          + (@days % 5) 
          + (CASE WHEN ((@days%5) + DATEPART(dw, @date)) IN (1,7,8,9,10) THEN 2 ELSE 0 END)
        , @date) AS Date
)

内部功能类似于戈登·利诺夫（Gordon Linoff）的解决方案，除了它考虑了跨越周末界限但没有跨越整周界限的日期。

The "inner" function is similar to Gordon Linoff's solution, except it accounts for dates crossing weekend boundaries but without crossing a full week boundary.

最后，我创建了一个测试脚本进行测试我的功能。期望值是使用 Patrick McDonald出色的C＃等价物生成的，我对此进行了随机交叉引用此流行的计算器的数据。

Finally, I created a test script to test my function. The expected values were generated using Patrick McDonald's excellent C# equivalent and I randomly cross-referenced this data with this popular calculator.

这篇关于如何使用SQL Server增加某些日期（不包括周末）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！