A different Weekdays Between test


Recently, I answered a question by a person on Experts Exchange.com on how to find the # of weekdays between two dates. This question was simplified greatly by the fact that the dates given would always be from Monday to Friday, and so the calculations needed were very simple.

Then, while looking at the recent scripts listing here on SQL Server Central, I saw Rick Carisse's function for calculating the number of weekdays between two dates, and realized that I really needed to flesh out the basic function to (1) handle the case of the start or end day falling on a weekend, and (2) to handle any Datefirst setting.

This UDF takes a start date and an end date, and returns the # of weekdays falling between them. If the days are the same and a weekday, they return 1 day.

-- By Brendt Hess
-- Inspired by work at www.experts-exchange.com, and by the script by Rick Carisse
-- This calculates the correct # of weekdays (defined as Monday through Friday
--   no matter what value is set for the first day of the week.

Create Function [dbo].[ufn_WeekdaysBetween]
	( @StartDate datetime,
	  @EndDate Datetime)
	-- Since I need to make an adjustment based on if the Start is on Sunday
	-- let's save those values into variables

	Declare @D1Day int
	Declare @D2Day int

	Set @D1Day = Datepart(dw, @StartDate)
	Set @D2Day = Datepart(dw, @EndDate)

	-- Since day of deek can vary depending on the Datefirst value, we need to calculate
	-- the actual Day of Week values for Sunday and Saturday.
	-- To simplify, we use a constant date known to be a Sunday

	Declare @dSunday int
	Declare @dSaturday int

	Set @dSaturday = datepart(dw, Convert(smalldatetime, '2000-01-01'))
	Set @dSunday = @dSaturday + 1
	If @dSunday > 7
		Set @dSunday = 1

	-- Now that we have these values, we are ready to calculate the number of days.
	-- Basic formula is:  # of days apart the dates are Plus 1 minus (2 * # of weeks apart)
	-- 	If the start date is on Sunday, subtract one day.
	--	If the end date is on Saturday, subtract one day.
	--	The weekend adjustment takes care of all other cases.

	Declare @Days int

	Set @Days = DateDiff(d, @StartDate, @EndDate) 
			- DateDiff(ww, @StartDate, @EndDate) * 2 + 1 
			- CASE WHEN @D1Day = @dSunday Then 1 Else 0 END 
			- CASE WHEN @d2Day = @dSaturday Then 1 Else 0 END 

	Return @Days