Creating A Function To Return Total Business Days Between Two Dates
A part of my job involves creating reports out of our ERP system for various departments. Quite often this involves creating ad hoc reports. When this is the case I try to minimize the time it takes to develop these reports. As a result I have created quite a collection of scripts to handle various query requirements that tend to be relevant when creating these one-off reports.
As most database developers would agree, date manipulations within SQL tends to be one of the more tedious and annoying concepts. However, once you’re able to wrap your head around the various techniques, it gets to be a bit easier to work with. Nonetheless, I make it a rule to always save my code as I know it will be useful to call on it at a later time.
One concept I have come to call on time and time again in developing reports is the need to calculate the total number of business days between a certain date range. When I find myself needing to perform the same task over and over I like to take it one step further and create a User Defined Function (UDF) to allow me to easily pass the dates and get the output without having to reinvent the wheel within the script or procedure I am writing.
The following code will handle this for you by creating a UDF named fnGetWorkDays
and allowing you to pass at starting date and ending date. The output will simply be an integer representing the total number of business/working days between the two dates.
CREATE FUNCTION fnGetWorkDays(@startdate DATETIME, @stopdate DATETIME)
RETURNS INT
AS
BEGIN
DECLARE @begindate DATETIME,
@enddate DATETIME,
@days INT
SET @begindate = @startdate
SET @enddate = @stopdate
SET @days= (SELECT
(DATEDIFF(dd, @begindate, @enddate) + 1)
-(DATEDIFF(wk, @begindate, @enddate) * 2)
-(CASE WHEN DATENAME(dw, @begindate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @enddate) = 'Saturday' THEN 1 ELSE 0 END))
RETURN @days
END
Once this function has been created and pushed to your database you can call it within a script or procedure and use the output as needed.
If you simply want to test the function you can do this using the following select statement:
SELECT dbo.fnGetWorkDays('12/21/2015','12/27/2015')
The output should display the following.
We can take this a step further and pass DATETIME
variables to the function, a more realistic, real-world application.
DECLARE @startdate DATETIME,
@enddate DATETIME
SET @startdate = '12/21/2015'
SET @enddate = '12/27/2015'
SELECT dbo.fnGetWorkDays(@startdate,@enddate)