在没有循环的情况下在 SQL 中添加迄今为止的工作日

Add business days to date in SQL without loops

提问by Matt King

I currently have a function in my SQL database that adds a certain amount of business days to a date, e.g. if you enter a date that is a Thursday and add two days, it will return the date of the following Monday. I'm not bothered about any holidays, only weekends are excluded.

我目前在我的 SQL 数据库中有一个函数可以将一定数量的工作日添加到某个日期，例如，如果您输入一个星期四的日期并添加两天，它将返回下一个星期一的日期。我不关心任何假期，只有周末被排除在外。

The problem is that this is currently done using a while loop, and it appears to be massively slowing down the stored procedure that uses it while generating a table. Does anyone know if there is any way to perform this calculation without while loops or cursors?

问题是目前这是使用 while 循环完成的，并且它似乎大大减慢了在生成表时使用它的存储过程的速度。有谁知道是否有任何方法可以在没有 while 循环或游标的情况下执行此计算？

Just for information, this is the current function:

仅供参考，这是当前的功能：

ALTER FUNCTION [dbo].[AddWorkDaysToDate]
(   
@fromDate       datetime,
@daysToAdd      int
)
RETURNS datetime
AS
BEGIN   
DECLARE @toDate datetime
DECLARE @daysAdded integer

-- add the days, ignoring weekends (i.e. add working days)
set @daysAdded = 1
set @toDate = @fromDate

while @daysAdded <= @daysToAdd
begin
    -- add a day to the to date
    set @toDate = DateAdd(day, 1, @toDate)
    -- only move on a day if we've hit a week day
    if (DatePart(dw, @toDate) != 1) and (DatePart(dw, @toDate) != 7)
    begin
        set @daysAdded = @daysAdded + 1
    end
end

RETURN @toDate

END

采纳答案by Damien_The_Unbeliever

This answer has been significantly altered since it was accepted, since the original was wrong. I'm more confident in the new query though, and it doesn't depend on DATEFIRST

这个答案自从被接受以来已经有了很大的改变，因为原来是错误的。不过，我对新查询更有信心，而且它不依赖于DATEFIRST

---

---

I think this should cover it:

我认为这应该涵盖它：

declare @fromDate datetime
declare @daysToAdd int

select @fromDate = '20130123',@DaysToAdd = 4

declare @Saturday int
select @Saturday = DATEPART(weekday,'20130126')

;with Numbers as (
    select 0 as n union all select 1 union all select 2 union all select 3 union all select 4
), Split as (
    select @DaysToAdd%5 as PartialDays,@DaysToAdd/5 as WeeksToAdd
), WeekendCheck as (
    select WeeksToAdd,PartialDays,MAX(CASE WHEN DATEPART(weekday,DATEADD(day,n.n,@fromDate))=@Saturday THEN 1 ELSE 0 END) as HitWeekend
    from
    Split t
        left join
    Numbers n
        on
            t.PartialDays >= n.n
group by WeeksToAdd,PartialDays
)
select DATEADD(day,WeeksToAdd*7+PartialDays+CASE WHEN HitWeekend=1 THEN 2 ELSE 0 END,@fromDate)
from WeekendCheck

We split the time to be added into a number of weeks and a number of days within a week. We then use a small numbers table to work out if adding those few days will result in us hitting a Saturday. If it does, then we need to add 2 more days onto the total.

我们将要添加的时间分为几周和一周内的几天。然后我们使用一个小数字表来计算添加这几天是否会导致我们到达星期六。如果是这样，那么我们需要在总数上再增加 2 天。

回答by ElmerMiller

This is better if anyone is looking for a TSQL solution. No loops, no tables, no case statements AND works with negatives. Can anyone beat that?

如果有人正在寻找 TSQL 解决方案，这会更好。没有循环，没有表格，没有 case 语句，并且适用于否定。有人能打败它吗？

CREATE FUNCTION[dbo].[AddBusinessDays](@Date date,@n INT)
RETURNS DATE AS 
BEGIN
DECLARE @d INT;SET @d=4-SIGN(@n)*(4-DATEPART(DW,@Date));
RETURN DATEADD(D,@n+((ABS(@n)+@d-2)/5)*2*SIGN(@n)-@d/7,@Date);
END

回答by Nate Cook

Building off of the answer that was accepted for this question, the following user-defined function (UDF) should work in all cases--regardless of the setting for @@DateFirst.

基于此问题已接受的答案，以下用户定义函数 (UDF) 应适用于所有情况——无论@@DateFirst.

UPDATE: As comments below indicate, this function is designed for the FromDate to be a weekday. The behavior is undefined when a weekend day is passed in as the FromDate.

更新：正如下面的评论所示，这个函数是为 FromDate 设计的，它是一个工作日。当周末作为 FromDate 传入时，行为未定义。

ALTER FUNCTION [dbo].[BusinessDaysDateAdd] 
(
   @FromDate datetime,
   @DaysToAdd int
)
RETURNS datetime
AS
BEGIN
   DECLARE @Result datetime

   SET @Result = DATEADD(day, (@DaysToAdd % 5) + CASE ((@@DATEFIRST + DATEPART(weekday, @FromDate) + (@DaysToAdd % 5)) % 7)
                                                 WHEN 0 THEN 2
                                                 WHEN 1 THEN 1
                                                 ELSE 0 END, DATEADD(week, (@DaysToAdd / 5), @FromDate))

   RETURN @Result
END

回答by Juan M. Elosegui

This answers is based on @ElmerMiller's answer.

此答案基于@ElmerMiller 的答案。

It fixes the negative value on Sunday comment from @FistOfFury

它修复了@FistOfFury 周日评论的负值

Negative values don't work if the date passed in is Sunday

如果传入的日期是星期日，则负值不起作用

And the DATEFIRST setting comment from @Damien_The_Unbeliever

来自@Damien_The_Unbeliever 的 DATEFIRST 设置评论

But this one does assume a particular DATEFIRST setting (7), which some of the others don't need.

但是这个确实假设了一个特定的 DATEFIRST 设置 (7)，而其他一些不需要。

Now the corrected function

现在修正的功能

CREATE FUNCTION[dbo].[AddBusinessDays](@Date DATE,@n INT)
RETURNS DATE AS 
BEGIN
DECLARE @d INT,@f INT,@DW INT;
SET @f=CAST(abs(1^SIGN(DATEPART(DW, @Date)-(7-@@DATEFIRST))) AS BIT)
SET @DW=DATEPART(DW,@Date)-(7-@@DATEFIRST)*(@f^1)+@@DATEFIRST*(@f&1)
SET @d=4-SIGN(@n)*(4-@DW);
RETURN DATEADD(D,@n+((ABS(@n)+(@d%(8+SIGN(@n)))-2)/5)*2*SIGN(@n)-@d/7,@Date);
END

回答by Vick

*I know this is an old thread but found something extremely useful a while ago, modified it and got this.

*我知道这是一个旧线程，但不久前发现了一些非常有用的东西，对其进行了修改并得到了这个。

select ((DATEADD(d,DATEDIFF(d,0,(DATEADD (d,2,@fromDate))),@numbOfDays)))*

Update: I am sorry in a haste to find a piece of code (in a single statement) and to avoid using a function, I posted incorrect code here.

更新：我很抱歉匆忙找到一段代码（在单个语句中）并避免使用函数，我在这里发布了错误的代码。

Bit mentioned above can be used if the number of days you are adding is 7 or less.

如果您添加的天数为 7 或更少，则可以使用上述位。

I have changed the code with required parameters for better understanding.

为了更好地理解，我已使用所需参数更改了代码。

Anyway, I ended up using what 'Nate Cook' has mentioned above. And used it as a single line of code. (Because I am restraining from using functions)

无论如何，我最终使用了上面提到的“Nate Cook”。并将其用作单行代码。（因为我限制使用函数）

Nate's code

内特的代码

select(
DATEADD(day, (@days % 5) + 
CASE ((@@DATEFIRST + DATEPART(weekday, GETDATE()) + (@days % 5)) % 7)
WHEN 0 THEN 2
WHEN 1 THEN 1
ELSE 0 END, DATEADD(week, (@days / 5), GETDATE()))
)

回答by bleeeah

Have you thought about pre-populating a look-up table that contains all of the working days (using your function) , for example WorkingDays(int DaySequenceId, Date WorkingDate), you can then use this table by selecting the DaySequenceId of the @fromDate and add @daysToAdd to get the new working date. Obviously this method also has the additional overhead of administering the WorkingDays table, but you could pre-populate it with the range of dates you expect. The other downside is the working dates that can be calculated will only be those contained within the WorkingDays table.

您是否考虑过预先填充包含所有工作日的查找表（使用您的函数），例如 WorkingDays(int DaySequenceId, Date WorkingDate)，然后您可以通过选择 @fromDate 的 DaySequenceId 来使用此表并添加@daysToAdd 以获取新的工作日期。显然，此方法还具有管理 WorkingDays 表的额外开销，但您可以使用您期望的日期范围预先填充它。另一个缺点是可以计算的工作日期只能是 WorkingDays 表中包含的那些。

回答by FistOfFury

To expand on Amine's comment and Nate cook's answer above, the one-liner solution to this is:

为了扩展上面 Amine 的评论和 Nate Cook 的回答，对此的单行解决方案是：

declare @DaysToAdd int , @FromDate datetime 
set @DaysToAdd=-5      --5 days prior is 3/28/14
set @FromDate='4/4/14'
select 
    DATEADD(day, (@DaysToAdd % 5) 
    +   CASE 
        WHEN ((@@DATEFIRST + DATEPART(weekday, @FromDate)) % 7 + (@DaysToAdd % 5)) > 6 THEN 2 
        ELSE 0 
        END
    , DATEADD(week, (@DaysToAdd / 5), @FromDate))

Note you can add or subtract days to go forwards and backwards in time, respectively.

请注意，您可以添加或减去天数以分别在时间上前进和后退。

回答by Arjen

CREATE FUNCTION DateAddBusinessDays
(
    @Days int,
    @Date datetime  
)
RETURNS datetime
AS
BEGIN
    DECLARE @DayOfWeek int;

    SET @DayOfWeek = CASE 
                        WHEN @Days < 0 THEN (@@DateFirst + DATEPART(weekday, @Date) - 20) % 7
                        ELSE (@@DateFirst + DATEPART(weekday, @Date) - 2) % 7
                     END;

    IF @DayOfWeek = 6 SET @Days = @Days - 1
    ELSE IF @DayOfWeek = -6 SET @Days = @Days + 1;

    RETURN @Date + @Days + (@Days + @DayOfWeek) / 5 * 2;
END;

This function can add and subtract business days regardless of the value of @@DATEFIRST. To subtract business days use a negative number of days.

无论@@DATEFIRST 的值如何，此函数都可以添加和减去工作日。要减去工作日，请使用负天数。

回答by Deepak

WITH get_dates
AS
(
    SELECT getdate() AS date, 0 as DayNo 
    UNION ALL
    SELECT date + 1 AS date, case when DATEPART(DW, date + 1) IN (1,7) then DayNo else DayNo + 1 end
    FROM get_dates
    WHERE DayNo < 4
)
SELECT max(date) FROM get_dates
OPTION (MAXRECURSION 0) 

回答by Davin C

I have tested all of the solutions proposed here and none of them work. Here are some test scenarios that broke a lot of the above solutions. (assuming Saturday and Sunday are the days you are excluding):

我已经测试了这里提出的所有解决方案，但没有一个有效。以下是一些测试场景，它们打破了上述许多解决方案。（假设周六和周日是您排除的天数）：

-Add 0 days to a Saturday - Expected result = Saturday

- 将 0 天添加到星期六 - 预期结果 = 星期六

-Add 0 days to a Sunday - Expected result = Sunday

- 将 0 天添加到星期日 - 预期结果 = 星期日

-Add 1 day to Friday - Expected result = the following Monday

- 周五增加 1 天 - 预期结果 = 下周一

-Add 1 day to Saturday - Expected result = the following Monday

- 周六增加 1 天 - 预期结果 = 下周一

-Add 1 day to Sunday - Expected result = the following Monday

- 周日增加 1 天 - 预期结果 = 下周一

-Add 3 days to Friday - Expected result = the following Wednesday

- 周五增加 3 天 - 预期结果 = 下周三

-Add 5 days to Saturday - Expected result = the following Friday

- 星期六增加 5 天 - 预期结果 = 下一个星期五

-Add 5 days to Friday - Expected result = the following Friday

- 增加 5 天到星期五 - 预期结果 = 下一个星期五

-Subtract 1 day from Monday - Expected result = the previous Friday

- 从星期一减去 1 天 - 预期结果 = 前一个星期五

-Subtract 1 day from Sunday - Expected result = the previous Friday

- 从星期日减去 1 天 - 预期结果 = 前一个星期五

-Subtract 1 day from Saturday - Expected result = the previous Friday

- 从星期六减去 1 天 - 预期结果 = 前一个星期五

-Subtract 3 days from Monday - Expected result = the previous Wednesday

- 从星期一减去 3 天 - 预期结果 = 前一个星期三

-Subtract 5 days from Saturday - Expected result = the previous Monday

- 从星期六减去 5 天 - 预期结果 = 前一个星期一

-Subtract 5 days from Monday - Expected result = the previous Monday

- 从星期一减去 5 天 - 预期结果 = 前一个星期一

Here is what I wrote after reading this entire thread and picking the good pieces of logic:

这是我在阅读了整个线程并挑选了好的逻辑部分后写的：

CREATE FUNCTION [dbo].[BusinessDateAdd]
(
    @FromDate DATE
    ,@DaysToAdd INT
)
RETURNS DATE 
AS 
BEGIN

    --If there are no days to add or subtract, return the day that was passed in
    IF @DaysToAdd = 0 RETURN @FromDate

    DECLARE @Weeks INT
    DECLARE @DMod INT
    DECLARE @FromDateIndex INT

    --number of weeks
    SET @Weeks = @DaysToAdd/5

    --remainder of days
    SET @dmod = @DaysToAdd%5

    --Get the FromDate day of the week, this logic standardizes the @@DateFirst to Sunday = 1
    SET @FromDateIndex = (DATEPART(weekday, @FromDate) + @@DATEFIRST - 1) % 7 + 1

    /*Splitting the addition vs subtraction logic for readability*/

    --Adding business days
    IF @DaysToAdd > 0 
        BEGIN 

            --If the FromDate is on a weekend, move it to the previous Friday
            IF @FromDateIndex IN(1,7) 
                BEGIN
                    SET @FromDate = DATEADD(dd,CASE @FromDateIndex WHEN 1 THEN -2 WHEN 7 THEN -1 END,@FromDate)
                    SET @FromDateIndex = 6
                END

            SET @FromDate = DATEADD(dd, 
                CASE 
                    --If the mod goes through the weekend, add 2 days to account for it
                    WHEN 
                        ((@FromDateIndex = 3 --Tuesday
                        AND @dmod > 3) --Days until Friday
                        OR
                        (@FromDateIndex = 4  --Wednesday
                        AND @dmod > 2)--Days until Friday
                        OR 
                        (@FromDateIndex = 5 --Thursday
                        AND @dmod > 1)--Days until Friday
                        OR 
                        (@FromDateIndex = 6 --Friday
                        AND @dmod > 0))--Days until Friday
                        THEN 
                            @DMod+2 
                    --Otherwise just add the mod
                    ELSE 
                        @DMod 
                END, @FromDate)

        END

    --Subtracting business days
    IF @DaysToAdd < 0 
        BEGIN 

            --If the FromDate is on a weekend, move it to the next Monday
            IF @FromDateIndex IN(1,7) 
                BEGIN
                    SET @FromDate = DATEADD(dd,CASE @FromDateIndex WHEN 1 THEN 1 WHEN 7 THEN 2 END,@FromDate)
                    SET @FromDateIndex = 2
                END

            SET @FromDate = DATEADD(dd, 
                CASE 
                    --If the mod goes through the weekend, subtract 2 days to account for it
                    WHEN 
                        ((@FromDateIndex = 5 --Thursday
                        AND @dmod < -3) --Days until Monday
                        OR
                        (@FromDateIndex = 4  --Wednesday
                        AND @dmod < -2)--Days until Monday
                        OR 
                        (@FromDateIndex = 3 --Tuesday
                        AND @dmod < -1)--Days until Monday
                        OR 
                        (@FromDateIndex = 2 --Monday
                        AND @dmod < 0))--Days until Monday
                        THEN 
                            @DMod-2 
                    --Otherwise just subtract the mod
                    ELSE 
                        @DMod 
                END, @FromDate)

        END

    --Shift the date by the number of weeks
    SET @FromDate = DATEADD(ww,@Weeks,@FromDate)

    RETURN @FromDate

END