How to calculate work days except Saturday and Sunday

Script shows the complete definition for a user defined function that calculates working days by mostly using the DATEDIFF function.

CREATE FUNCTION [dbo].[fn_GetTotalWorkingDays]

(

    @DateFrom Date,

    @DateTo Date

)

RETURNS INT

AS

BEGIN

    DECLARE @TotDays INT= DATEDIFF(DAY, @DateFrom, @DateTo) + 1;

    DECLARE @TotWeeks INT= DATEDIFF(WEEK, @DateFrom, @DateTo) * 2;

    DECLARE @IsSunday INT= CASE

WHEN DATENAME(WEEKDAY, @DateFrom) = 'Sunday'

THEN 1

ELSE 0

  END;

    DECLARE @IsSaturday INT= CASE

   WHEN DATENAME(WEEKDAY, @DateTo) = 'Saturday'

   THEN 1

   ELSE 0

    END;

    DECLARE @TotWorkingDays INT= @TotDays - @TotWeeks - @IsSunday + @IsSaturday;

    RETURN @TotWorkingDays;

END

 

Example :