I was born in Bulgaria. My immediate family and I relocated to Syracuse, NY in 1995. I completed high school in Syracuse, and then continued my education at Alfred University. My major at Alfred University was Computer Science. I also obtained a minor in Management Information Systems (MIS) to bridge the gap between technology and the business world. One of my future goals is to extend that bridge by obtaining a Master's degree in Business Administration. Boyan is a DZone MVB and is not an employee of DZone and has posted 27 posts at DZone. You can read more from them at their website. View Full User Profile

Get Business Days and Working Hours in a Month

02.07.2008
| 21054 views |
  • submit to reddit
In business applications, there often is a need to know the number of business days and/or hours in a month/year. There is a dozen ways to do this depending on your language but here is a SQL Server implementation.The function to get the number of working hours in a month, simply uses the first function - dbo.fnGetBusinessDaysInMonth and by default multiplies the number of business days by 8 hours a day.

To use these custom functions, you would call them as follows:
-- Get the number of business days in the month
-- using the current date as a base select dbo.fnGetBusinessDaysInMonth(getdate()) -- Outputs 21 -- Get the number of business days for January,
-- the date can be any January date select dbo.fnGetBusinessDaysInMonth('01/01/2008') -- Outputs 23 -- Get the number of working hours for the current month
-- with the default (8) number of hours per day select dbo.fnGetWorkingHoursInMonth(getdate(), null) -- Outputs 168 -- Get the number of working hours for the current month
-- with 4 of hours per day select dbo.fnGetWorkingHoursInMonth(getdate(), 4) -- Outputs 84

And here are the function definitions:
/*
Type:		Function
Name:		dbo.fnGetBusinessDaysInMonth
Author:		Boyan Kostadinov
Created:	02.05.2007
Dependencies:	None
Usage:		select dbo.fnGetBusinessDaysInMonth(getdate())
		select dbo.fnGetBusinessDaysInMonth('01/01/2008')
Parameters:	@currentDate(datetime)
		- The date to use as a starting point
Description:

Gets the number of business days in a month
*/
alter function dbo.fnGetBusinessDaysInMonth(
	@currentDate datetime
)
returns int
as
begin

declare @dateRange int
declare @beginningOfMonthDate datetime, @endOfMonthDate datetime

-- Get the beginning of the month
set @beginningOfMonthDate = dateadd(month, -1, dateadd(day, -1, dateadd(month, datediff(month, 0, @currentDate) + 1, 1)))

-- Get the the beginning date of the next month
set @endOfMonthDate = dateadd(day, -1, dateadd(month, datediff(month, 0, @currentDate) + 1, 1))

-- Get the date range between the beginning and the end of the month
set @dateRange = datediff(day, @beginningOfMonthDate, @endOfMonthDate)

return
(
	-- Get the number of business days by getting the number
	-- of full weeks * 5 days a week plus the number days remaining
	-- minus any days from the remaining days that are a weekend day
	select	@dateRange / 7 * 5 + @dateRange % 7 -  
	(
	        select	count(*)
		from
	        (
	            select 1 as d
	            union
	            select 2
	            union
	            select 3
	            union
	            select 4
	            union
	            select 5
	            union
	            select 6
	            union
	            select 7
	        ) weekdays
	        where	d <= @dateRange % 7
		        and
			datename(weekday, dateadd(day, -1, @endOfMonthDate) - d)
		        in ('Saturday', 'Sunday')
	)
)

end
To get the number of working hours in a month, we can use the dbo.fnGetWorkingHoursInMonth function defined as:
/*
Type:		Function
Name:		dbo.fnGetWorkingHoursInMonth
Author:		Boyan Kostadinov
Created:	02.05.2007
Dependencies:	None
Usage:		select dbo.fnGetWorkingHoursInMonth(getdate(), null)
		select dbo.fnGetWorkingHoursInMonth('01/01/2008', 4)
Parameters:	@currentDate(datetime)
		- The date to use as a starting point

		@workingHoursInADay(int) - Optional, default is 8
		- The number of working hours in a day
Description:

Gets the number of business days in a month
*/
create function dbo.fnGetWorkingHoursInMonth(
	@currentDate datetime,
	@workingHoursInADay int = null
)
returns int
as

begin

if @workingHoursInADay is null
	set @workingHoursInADay = 8

return dbo.fnGetBusinessDaysInMonth(@currentDate) * @workingHoursInADay

end
References
Published at DZone with permission of Boyan Kostadinov, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)