Here's another function that takes a simpler approach. If you are maintaining a holiday table, it would be simple enough to check the return value against it as well.

John

CREATE       FUNCTION dbo.udf_GetNextBizDay (@dDateValue DATETIME)
RETURNS Datetime
AS 
---------------------------------------------------------------------------------
--
-- Modification History:
--
-- Date                     Version  Programmer Comments
-- ------------------       -------  ---------- ----------------------------
-- 2005-06-13 02:23:00  1.0  John McLaughlin 
--
--
BEGIN

 DECLARE @dReturnDate DATETIME
 

 IF ISDATE(@dDateValue)=0
  BEGIN
   RETURN NULL
  END
 
 SET @dReturnDate = DATEADD(d,1,@dDateValue)
 --Set to zero hour. Uncomment if needed.
 --SET @dReturnDate = CAST(CONVERT(CHAR(10),@dReturnDate,101) AS DATETIME)
 WHILE DATEPART(dw,@dReturnDate) in(1,7)
  BEGIN
   SET @dReturnDate = DATEADD(d,1,@dReturnDate)
  END
 

 RETURN @dReturnDate
END

I like the trick for handling the DATEFIRST.  With it, you can write the function in 3 lines without recursion, which I think is a bit more elegant.

CREATE FUNCTION dbo.fnGetNextBusinessDay (@Date datetime) RETURNS datetime AS
BEGIN
     SET @Date = DATEADD(dd, 1, CONVERT(datetime, (CONVERT(CHAR(10), @Date, 101))))
     WHILE ((@@DATEFIRST + DATEPART(dw, @Date) - 1) % 7) + 1 IN (1, 7) OR @Date IN (SELECT HolidayDate FROM Holiday) SET @Date = DATEADD(dd, 1, @Date)
     RETURN @Date
END

Hey Joe, don't you feel like you are talking for nothing sometimes???

Guys, Joe literally wrote the book on SQL, he wrote the ISO standards for SQL.  I would strongly suggest you listen to him on this one... and basically any other post he makes .

Even if it seems like overkill this time, and the next time and the time after that.  Won't you wish you had taken the time to write one when you decid to implement it the nth time... and then decid to make that the business rule to implement overall once you see the benefits??

I don't understand the @numDays input parameter!

For example if do this,

DECLARE @Date SMALLDATETIME

SET @Date = dbo.fnGetNextBusinessDay ('2006-12-24', 10)

SELECT @Date

It's suppsoe to return the date 2006-12-27 as that is the next business day.

Can someone explain please?

Hi Trigger,

The @numDays parameter is the number of business days to move forward in time.

To get 2006-12-27, you should have used:

SET @Date = dbo.fnGetNextBusinessDay ('2006-12-24', 1)

to get the "first" next business day. Your use of:

SET @Date = dbo.fnGetNextBusinessDay ('2006-12-24', 10)

should get the 10th next business day.

This did not do it for me:

select dbo.GetNextBusinessDay(getdate(),1)
select dbo.GetNextBusinessDay(getdate()+1,1) -- Fri
select dbo.GetNextBusinessDay(getdate()+2,1) -- Sat
select dbo.GetNextBusinessDay(getdate()+3,1) -- Sun

                                                     
------------------------------------------------------
2007-04-27 12:41:00

(1 row(s) affected)

                                                      
------------------------------------------------------
2007-04-30 12:41:00

(1 row(s) affected)

                                                      
------------------------------------------------------
2007-04-29 12:41:00

(1 row(s) affected)

                                                      
------------------------------------------------------
2007-04-30 12:41:00

(1 row(s) affected)