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
CREATE FUNCTION dbo.fn_WeekDays
@param_NoofHoliday INT = 0
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)
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
-- Function execution
SET DATEFORMAT DMY
SELECT dbo.fn_WeekDays(CONVERT(DATETIME, '01-03-2012'),
0) AS [No of Week Days]
Hope you like it.
Posted by: MR. JOYDEEP DAS