DATEDIFF for business days only

[CORVA]

I'm looking for a DATEDIFF function which will give me the
BUSINESS days difference between 2 dates.
e.g: datediff(day,'08/08/2007','08/14/2007) would normally result in 6 (i think), however in business days this would be 4.
is there such a function?
Thx

[rmiao]

No such builtin function in sql server.

[CORVA]

Thx. any suggestions?

[nosepicker]

I wrote this a long time ago, but I haven't tested it thoroughly. I know it works for most situations, but I don't know about all. Try it and see if it works for you:


declare @startdate datetime,
@enddate datetime

set @startdate = convert(datetime, '20070808')
set @enddate = convert(datetime, '20070814')

SELECT
DATEDIFF( d,
DATEADD( d,
CASE WHEN DATEPART(dw, @startdate) = 1 THEN 1 WHEN DATEPART(dw, @startdate) = 7 THEN 2 ELSE 0 END,
@startdate
),
DATEADD( d,
CASE WHEN DATEPART(dw, @enddate) = 1 THEN 1 WHEN DATEPART(dw, @enddate) = 7 THEN 2 ELSE 0 END,
@enddate
)
)
- DATEDIFF( wk,
DATEADD( d,
CASE WHEN DATEPART(dw, @startdate) = 1 THEN 1 WHEN DATEPART(dw, @startdate) = 7 THEN 2 ELSE 0 END,
@startdate
),
DATEADD( d,
CASE WHEN DATEPART(dw, @enddate) = 1 THEN 1 WHEN DATEPART(dw, @enddate) = 7 THEN 2 ELSE 0 END,
@enddate
)
) * 2

[mxhxr]

We had a similar problem. You could build a nonBizDays table with only non-business days in it. This table could be used in date queries to determine dates. Google will reveal some queries to load this table.