IF 	EXISTS (SELECT * FROM sysobjects WHERE xtype = 'FN' AND name = 'fn_next_business_day')
BEGIN
	DROP FUNCTION fn_next_business_day
END
go

CREATE FUNCTION fn_next_business_day
(
	@start_date	datetime,
	@days		int       -- no of business days to add
)
RETURNS datetime
AS
BEGIN
	DECLARE	@wd	int

	--	get the weekday AND CONVERT to datefirst = 1 value
	SELECT	@wd	= ((DATEPART(weekday, @start_date) - 1 + 7 - (8 - @@datefirst)) % 7) + 1

	--	IF it IS Sat, Sun, change teh date to Next Monday
	IF	@wd	IN (6, 7)
	BEGIN
		SELECT	@start_date 	= DATEADD(DAY, 7 - @wd + 1, @start_date)
		SELECT	@wd		= ((DATEPART(weekday, @start_date) - 1 + 7 - (8 - @@datefirst)) % 7) + 1
	END

	RETURN
	(
		SELECT	DATEADD(DAY, 
				@days	+ CASE	WHEN 	@days >= (5 - @wd + 1)
						THEN	((@days + ((DATEPART(weekday, @start_date) - 1 + 7 - (8 - @@datefirst)) % 7) + 1 - 1)/ 5) * 2
						ELSE	0
						END,
				@start_date)
	)
END
go

/* testing */
DECLARE
	@start_date	datetime

SELECT	@start_date	= '2009-05-11'

SELECT	start_date	= d.start_date,
	start_wd	= DATENAME(weekday, d.start_date),
	days		= d.days,
	business_day	= dbo.fn_next_business_day(d.start_date, d.days),
	business_wd	= DATENAME(weekday, dbo.fn_next_business_day(d.start_date, d.days))
FROM	(
		SELECT	start_date	= DATEADD(DAY, s.NUMBER, @start_date),
			days		= d.NUMBER
		FROM	(
				SELECT	NUMBER = 0 UNION ALL SELECT NUMBER = 1 UNION ALL SELECT NUMBER = 2 UNION ALL
				SELECT 	NUMBER = 3 UNION ALL SELECT NUMBER = 4 UNION ALL SELECT NUMBER = 5 UNION ALL
				SELECT 	NUMBER = 6
			) s
			CROSS JOIN dbo.F_TABLE_NUMBER_RANGE(0, 15) d
	) d
ORDER BY d.start_date, d.days

CREATE FUNCTION	dbo.fnNextBusinessDay
(
	@theDate DATETIME,
	@theDays SMALLINT
)
RETURNS DATETIME
AS
BEGIN
	DECLARE	@Offset TINYINT,
		@ApproxDate DATETIME

	SELECT	@ApproxDate = DATEADD(DAY, @theDays, @theDate),
		@Offset = DATEDIFF(DAY, -53690, @ApproxDate) % 7
		
	RETURN	DATEADD(DAY, (@Offset / 5) * (7 - @Offset), @ApproxDate)
END

CREATE FUNCTION	dbo.fnNextBusinessDay
(
	@theDate DATETIME,
	@theDays SMALLINT
)
RETURNS DATETIME
AS
BEGIN
	RETURN	(
			SELECT	DATEADD(DAY, (Offset / 5) * (7 - Offset), ApproxDate)
			FROM	(
					SELECT	DATEADD(DAY, @theDays, @theDate) AS ApproxDate,
						DATEDIFF(DAY, -53690, DATEADD(DAY, @theDays, @theDate)) % 7 AS Offset
				) AS d
		)
END

quote:

---

I believe your function calculates the wrong date. Using your sample data, see rows with Days value {5,6,7,8} for StartDate of '20090511'.
Rows with Days value {6,7} should all be Monday 18th, right?

---

CREATE FUNCTION dbo.fnAddBusinessDays
(
	@Date DATETIME,
	@BusinessDays SMALLINT
)
RETURNS DATETIME
AS
BEGIN
	DECLARE	@Days SMALLINT

	SELECT	@Days = ABS(@BusinessDays),
		@BusinessDays = COALESCE(NULLIF(@BusinessDays, 0), 1)

	WHILE @Days > 0 OR DATEDIFF(DAY, -53690, @Date) % 7 / 5 = 1
		SELECT	@Date = DATEADD(DAY, SIGN(@BusinessDays), @Date),
			@Days = @Days + DATEDIFF(DAY, -53690, @Date) % 7 / 5 - 1

	RETURN	@Date
END

quote:

---

However this gives different result when starting on an weekend.
What is 0 business days added to a saturday or sunday?

---