1. Registered User
Join Date
Aug 2007
Posts
2

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

2. Registered User
Join Date
Sep 2002
Posts
5,938
No such builtin function in sql server.

3. Registered User
Join Date
Aug 2007
Posts
2
Thx. any suggestions?

4. Registered User
Join Date
Dec 2004
Posts
502
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,
CASE WHEN DATEPART(dw, @startdate) = 1 THEN 1 WHEN DATEPART(dw, @startdate) = 7 THEN 2 ELSE 0 END,
@startdate
),
CASE WHEN DATEPART(dw, @enddate) = 1 THEN 1 WHEN DATEPART(dw, @enddate) = 7 THEN 2 ELSE 0 END,
@enddate
)
)
- DATEDIFF( wk,
CASE WHEN DATEPART(dw, @startdate) = 1 THEN 1 WHEN DATEPART(dw, @startdate) = 7 THEN 2 ELSE 0 END,
@startdate
),
CASE WHEN DATEPART(dw, @enddate) = 1 THEN 1 WHEN DATEPART(dw, @enddate) = 7 THEN 2 ELSE 0 END,
@enddate
)
) * 2

5. Registered User
Join Date
Aug 2007
Posts
3
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.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•