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