BlogmyQuery – BMQ

This Example shows how to calculate the calculate the workdays between two given dates.

CREATE FUNCTION dbo.GetBusinessDays
(
@startDate SMALLDATETIME,
@endDate SMALLDATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @dateDiff INT;

SET @dateDiff = DATEDIFF(DAY, @startDate, @endDate)+1;

RETURN
(
SELECT @dateDiff/ 7 * 5 + CASE WHEN (@dateDiff % 7) = 0 THEN 0

ELSE

CASE
When (DATEPART(WEEKDAY, @endDate ) + @@DateFirst -1)%7 = 0 and (@dateDiff % 7) =1 Then (@dateDiff % 7)-1 –If it is sunday

When (DATEPART(WEEKDAY, @endDate ) + @@DateFirst -1)%7 = 0 and (@dateDiff % 7) <>1 Then (@dateDiff % 7)-2 –If it is sunday

When (DATEPART(WEEKDAY, @endDate ) + @@DateFirst -1)%7 =6 Then (@dateDiff % 7) – 1   –If it is Saturday

ELSE (@dateDiff % 7)
END — Inner Case

END

);
END

There are many other ways of achieving this , you can also look at the following examples

CREATE FUNCTION dbo.GetWorkingDays
(
@startDate SMALLDATETIME,
@endDate SMALLDATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @range INT;

SET @range = DATEDIFF(DAY, @startDate, @endDate)+1;

RETURN
(
SELECT
@range / 7 * 5 + @range % 7 -
(
SELECT COUNT(*)
FROM
(
SELECT 1 AS d
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
) weekdays
WHERE d <= @range % 7
AND DATENAME(WEEKDAY, @endDate – d + 1)
IN
(
‘Saturday’,
‘Sunday’
)
)
);
END
GO

or this that i found on one of the forums

DECLARE @start_date DATETIME;
DECLARE @end_date DATETIME;

SET @start_date = '20080820';
SET @end_date = '20080825';

SELECT ((total_days / 7) * 5 + total_days % 7 -
CASE WHEN 6 BETWEEN start_weekday AND end_weekday
THEN 1 ELSE 0 END -
CASE WHEN 7 BETWEEN start_weekday AND end_weekday
THEN 1 ELSE 0 END)
FROM (SELECT total_days, start_weekday,
start_weekday + total_days % 7 - 1
FROM (SELECT DATEDIFF(day, @start_date, @end_date) + 1,
DATEPART(WEEKDAY, @start_date + @@DATEFIRST - 1)
) AS T(total_days, start_weekday)
) AS D(total_days, start_weekday, end_weekday);

NOTE : This example doesnot holidays other than saturday and sunday into account.