MS-SQL - Working Days Count in MS-SQL

The Following Script to count number of working days between two dates in MS-SQL.

Step 1
Create a User Defined WorkingDaysCount function,it is look like this

CREATE FUNCTION Dbo.WorkingDaysCount(@StartDate DATETIME,@EndDate DATETIME)
RETURNS INT
AS
    BEGIN
        
        DECLARE @CurrentDate DATETIME
        DECLARE @Days INT
        DECLARE @IncrementValue INT
        
            SET @Days=0
            SET @IncrementValue=0
        
                WHILE @IncrementValue<=(DATEDIFF(DAY,@StartDate,@EndDate))    
                    BEGIN
                            -- Get a Current Date between two date
                            SET @CurrentDate=DATEADD(DAY,@IncrementValue,@StartDate)
                            
                            IF (DATENAME(WEEKDAY,@CurrentDate) !='Sunday' )
                                BEGIN
                                        SET @Days=@Days+1 -- Increment Working Days Counts
                                END
                        
                        SET @IncrementValue=@IncrementValue+1
                        
                    END
                    
            RETURN @Days            
    END

Step 2
Call User defined WorkingDaysCount function,it is look like this

SELECT dbo.WorkingDaysCount('1/1/2011','12/31/2011') AS 'Working Days'

Note - Date must Specify on this format - MM/DD/YYYY

Output

Download
Download SQL Script