Number of Working Days

,

Select dbo.WorkDays()

For this to work you will need to create a table called Holidays and specify the date of each of your company holidays. Table will contain 1 field named Holiday and be a varchar(10) datatype, data will need to be entered in mm/dd/yyyy format. This function will work for the current month you are in.  You can modify as needed for any month you want.
Create FUNCTION [dbo].[WorkDays]
(
	-- Add the parameters for the function here
	 
)
RETURNS int
AS
BEGIN
	-- Declare the return variable here
	DECLARE @WorkDays int

	-- Add the T-SQL statements to compute the return value here
	SELECT @WorkDays = (SELECT COUNT(number+1)
												 FROM   master..spt_values 
														WHERE  type='p'
															AND    number <datepart(dd, DateAdd(day,-1,DateAdd(Month,1,DateAdd(Month,
																						  DateDiff(Month, 0, GETDATE()),0))))
															AND    datename(WEEKDAY,DateAdd(Month, DateDiff(Month, 0, GetDate()),
																						 number) ) not in ('Saturday','Sunday')
															AND number+1 NOT IN(SELECT DATEPART(DAY,HOLIDAY) FROM HOLIDAYS
													WHERE DATEPART(MONTH,HOLIDAY ) = DATEPART(MONTH,GETDATE())
													AND DATEPART(YEAR,HOLIDAY) = DATEPART(YEAR,GETDATE())))

	-- Return the result of the function
	RETURN @WorkDays

END

Rate

1.69 (13)

Share

Share

Rate

1.69 (13)