Calculating number of Weekdays

In this article there is a simple SQL statement that shows the number of week days between two supplied dates.

You can make it a function and supply 3 parameters like starts date, end dates and Number of Holiday. If you have a holiday list (or you know the number of holiday in years) you can calculates the total number of working dates in a year. 

Here in this example I am taking 0 holidays.

I am trying to make it a function, which you can directly use.

IF OBJECT_ID (N'dbo.Ifn_WeekDays', N'FN') IS NOT NULL

    DROP FUNCTION dbo.fn_WeekDays

GO

-- Function

CREATE FUNCTION dbo.fn_WeekDays

(    

      @param_StartDate   DATETIME,

      @param_EndtDate    DATETIME,

      @param_NoofHoliday INT = 0

)

RETURNS  INT

AS

BEGIN

      DECLARE  @start    As DateTime,

               @end      As DateTime,

               @holidy   As Int,

               @v_Retval As Int

            

      SET @start=CONVERT(datetime, @param_StartDate)

      SET @end  =CONVERT(datetime, @param_EndtDate)

      SET @holidy=@param_NoofHoliday

      SELECT @v_Retval =DATEDIFF(d,@start,@end)+1

               - (DATEDIFF(wk,@start,@end) + CASE WHEN DATEPART(dw,@start)=1            

                                                 THEN 1 ELSE 0 END )

               - (DATEDIFF(wk,@start,@end) + CASE WHEN DATEPART(dw,@end)=7

                                                 THEN 1 ELSE 0 END )- @holidy

            

      RETURN(@v_Retval)  

END

GO

-- Function execution

SET DATEFORMAT DMY

SELECT dbo.fn_WeekDays(CONVERT(DATETIME, '01-03-2012'),

                       CONVERT(DATETIME, '31-03-2012'),

                       0) AS [No of Week Days]

Hope you like it.

Posted by: MR. JOYDEEP DAS