Get Business Days and Working Hours in a Month with SQL Server

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