使用SQL Server将x天数添加到sql中的日期，但也要传递一周中的工作天数 [英] With SQL Server add x number of days to a date in sql but also pass in the number of business days in a week

问题描述

在SQL Server中，我想在日期中添加x个工作日，但还要传递一周中的工作日数。即可能是5,6，7。

In SQL Server I would like to add x number of business days to a date but also pass in the amount of business days in a week. ie could be 5,6 ,7.

我在处理5天的堆栈溢出中发现了这个，但是不确定如何修改它，以便您可以指定工作次数每周的天数。

I found this on stack overflow that handles 5 days but not sure how to modify it so that you could specify the number of working days per week.

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

推荐答案

就是这样，我有将其转换为存储过程以更好地理解。

This is it, I have converted it to a stored procedure for better understanding.

我使用过SQL Server。

I have used SQL Server.

Alter procedure [dbo].[AddBusinessDaysP]
(@Date date,@n INT,@wds INT)
as
BEGIN
--exec AddBusinessDaysP '9/25/2014',2,4

Declare @totWeekEnds int
Set @totWeekEnds = (Case When @n > 7 then (@n / 7) else 1 end)  * (7-@wds)

Declare @totDays int

Set @totDays = @n  + @totWeekEnds

Select @n as DaysToAdd,@wds as DaysInWeek,@totWeekEnds as TotalWeekEnds,@totDays as TotalDaysToAdd,Dateadd(dd,@totDays,@Date) as Answer

END

我还根据需要将SP转换为Function。

I have also converted SP into Function as you want.

Alter FUNCTION[dbo].[AddBusinessDays](@Date date,@n INT,@wds INT)
RETURNS DATE AS 
BEGIN
--Select [dbo].[AddBusinessDays]('9/25/2014',2,5)

Declare @totWeekEnds int
Set @totWeekEnds = (Case When @n > 7 then (@n / 7) else 1 end)  * (7-@wds)

Declare @totDays int

Set @totDays = @n  + @totWeekEnds

Return Dateadd(dd,@totDays,@Date) 

END

这篇关于使用SQL Server将x天数添加到sql中的日期，但也要传递一周中的工作天数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！