SQL Server获得X个工作日后的日期

项目需要获得在SQL Server中计算某个日期的X个工作日后的日期。要求排除周六日，并且排除System_holiday表中记录的节日（无调休，周六日一律休息）。

存储过程如下：

CREATE PROCEDURE sp_GetWorkingDate
    @inputDate DATE,
    @xDay INT,
    @targetDate DATE OUTPUT
AS
BEGIN
	
	WITH dates AS (
    	SELECT @inputDate AS normalDate, 1 AS num
    	UNION ALL
    	SELECT DATEADD(day, 1, normalDate), num + 1
    	FROM dates
    	WHERE num <= @xDay*3
	)

	select @targetDate = normalDate from (
    	SELECT ROW_NUMBER() OVER(ORDER BY num) AS row_num,  normalDate
		FROM dates
		WHERE DATENAME(weekday, normalDate) NOT IN ('Saturday', 'Sunday')
		AND NOT EXISTS (SELECT 1 FROM System_holiday WHERE SH_DATE = [normalDate])
    )as dateLst
    where row_num = @xDay+1
    
END

使用方法：

DECLARE @outputDate DATE
EXEC sp_GetWorkingDate '2023-12-13', 7, @outputDate OUTPUT
SELECT @outputDate

也可以不写存储过程，改为SqlServer的函数：

CREATE FUNCTION fn_GetWorkingDate
(
    @inputDate DATE,
    @xDay INT
)
RETURNS DATE
AS
BEGIN
	
	declare @targetDate DATE;
	
	WITH dates AS (
    	SELECT @inputDate AS normalDate, 1 AS num
    	UNION ALL
    	SELECT DATEADD(day, 1, normalDate), num + 1
    	FROM dates
    	WHERE num <= @xDay*3
	)

	select @targetDate = normalDate from (
	    	SELECT ROW_NUMBER() OVER(ORDER BY num) AS row_num,  normalDate
			FROM dates
			WHERE DATENAME(weekday, normalDate) NOT IN ('Saturday', 'Sunday')
			AND NOT EXISTS (SELECT 1 FROM System_holiday WHERE SH_DATE = [normalDate])
	    )as dateLst
	where row_num = @xDay+1;

	return @targetDate;
    
END

使用方法：

SELECT dbo.fn_GetWorkingDate('2023-12-13', 5) AS targetDate