number of business days in SQL

[Homerboy]

I'm attempting to use the coding provided under the "UDF to return the number of business days, including a check to a bank holidays table" (towards bottom) provided on the following page:

http://msdn.microsoft.com/en-us/library/ms189794(v=sql.90).aspx

I have created a "holidays" table with our 2011 holidays listed.
However, it seems to be giving me wrong data back.

using: dbo.fnGetBusinessDays('09/01/2011','09/30/2011') for September it returns 20 business days, when there are 21 (22 days of the week - 1 paid holiday)

using dbo.fnGetBusinessDays('10/01/2011','10/31/2011') for October it returns 21 (this is correct as there are no holidays)

using dbo.fnGetBusinessDays('11/01/2011','11/30/2011') for November it returns 19 business days, when there are 20 (22 days of the week - 2 paid holidays)

using dbo.fnGetBusinessDays('12/01/2011','12/31/2011') for December it returns 19 business days when there are 20 (22 days of the week - 2 paid holidays)

These are the entries in the bankholidays table:

2011-09-05 00:00:00.000
2011-11-24 00:00:00.000
2011-11-25 00:00:00.000
2011-12-23 00:00:00.000
2011-12-26 00:00:00.000


Anyone have any thoughts?

...or anyone have a BETTER way to calculate # of business days that includes holiday schedule?

Thanks in advance.

 

[MrChad]

When you use

dbo.fnGetBusinessDays('09/01/2011','09/30/2011')

it is probably getting translated to:

dbo.fnGetBusinessDays('09/01/2011 00:00:00','09/30/2011 00:00:00')

What you really want is

dbo.fnGetBusinessDays('09/01/2011 00:00:00','09/30/2011 23:59:59')

or

dbo.fnGetBusinessDays('09/01/2011 00:00:00','10/01/2011 00:00:00')

 

[Homerboy]

I thought of that myself, and tried it, but it throws off still:

Sept: dbo.fnGetBusinessDays('09/01/2011','10/01/2011') = 20
Oct: dbo.fnGetBusinessDays('10/01/2011','11/01/2011') = 21
Nov: dbo.fnGetBusinessDays('11/01/2011','12/01/2011') = 20

 

[KentState]

I would suggest creating a calendar table with all dates and then setting a smallint flags for things like holidays, weekends, etc. That way you can sum up the columns and do the math. You can also do translations between calendar quarters, months, and weeks which may be different from the business calendar.

 

[KLin]

meh I rewrote it

ALTER FUNCTION [dbo].[fnGetBusinessDays]
(
@startdate datetime,
@enddate datetime
)
RETURNS int
AS
BEGIN
DECLARE @days int
DECLARE @curdate datetime
DECLARE @datename as varchar(20)

set @curdate = @startdate
set @days = 0

while @curdate <= @enddate
BEGIN
set @datename = DATENAME(dw, @curdate)
	If (SELECT Count(*) from holidays where bankholiday = @curdate) = 0
		BEGIN
		If (@datename = 'Saturday' or @datename = 'Sunday')
			BEGIN
			SET @days = @days
			END
		Else
			SET @days = @days + 1
		END
		SET @curdate = dateadd(d, 1, @curdate)
END
RETURN (@days)

END

GO

 

[Homerboy]

sorry it took me a bit to get back.
I've tested this rewrite (and tried to follow along for that matter) and I THINK we're good to go. I test for September through Jan and it all matches up!

THANKS A BILLION!

 

[KLin]

np