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 WHENDATEPART(dw,@start)=1
THEN 1 ELSE 0 END )
- (DATEDIFF(wk,@start,@end) + CASE WHENDATEPART(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.
