quote:

---

Originally posted by Bustaz Kool

You'll need to change the RETURN, also. You could pass @NextDay in as another parameter and mark it as an "OUTPUT" type.

HTH

=================================================================
In order to improve the mind, we ought less to learn than to contemplate.
-Rene Descartes, philosopher and mathematician (1596-1650)

---

Create function NextWorkDay(@CurrDate datetime, @DaysToAdd int)
returns datetime
as
begin
	declare @WeeksToAdd int;
	declare @dw int;

	set @WeeksToAdd = (@DaysToAdd / 5) * 7
	set @DaysToAdd = @DaysToAdd % 5 
	
	set @CurrDate = DateAdd(dd,@WeeksToAdd + @DaysToAdd, @CurrDate)
	set @dw = datepart(dw,@CurrDate)

	if sign(@DaysToAdd) < 0 
		begin
		if @dw = 1 set @CurrDate = @CurrDate - 2
		if @dw = 7 set @CurrDate = @CurrDate - 1
		end
	else
		begin
		if @dw = 1 set @CurrDate = @CurrDate + 1
		if @dw = 7 set @CurrDate = @CurrDate + 2
		end

return @CurrDAte
end

ALTER FUNCTION dbo.udf_BusinessDayOfMonth
	(
	@CurDate datetime, -- must be passed in since UDF's cannot have getdate calls
	@DayNum int -- check for which business day
	)
RETURNS bit
AS
	BEGIN
		DECLARE @CurMonth int
		DECLARE @CheckDay datetime
		DECLARE @IsHoliday int
		DECLARE @RetVal bit

		SET @RetVal = 0
		SET @CheckDay = @CurDate
		SET @CurMonth = datepart (month, @CurDate)

		WHILE @DayNum > 0 
		BEGIN
			SET @CheckDay = dateadd (day, -1, @CheckDay)
	
			if datepart (month, @CheckDay) <> @CurMonth
			begin
				SET @RetVal = 1
				break
			end
			else
			begin
				SELECT @IsHoliday = count(*) FROM Holiday WHERE Holidate = @CheckDay

				if not (@IsHoliday = 1 OR datepart (weekday, @CheckDay) = 1 OR datepart (weekday, @CheckDay) = 7)
					SET @DayNum = @DayNum - 1
			end
		END
		
		RETURN @RetVal
	END