Calculate Weekdays Between Two Dates

24 Sep 2008

I came upon the need to calculate the number of weekdays between two dates. SQL Server offers several utilities for calculating dates, but nothing for this specific purpose. After staring at a calendar for some time and going through several revisions, I came up with a solution that seems to work pretty well. I do rely on 1 helper UDF, but it’s a pretty standard function that can easily be used elsewhere. Please note that I’ve designed the main function to be language independent—that is, it does not depend on @@DATEFIRST to be set any particular way.

First, the helper function:

CREATE FUNCTION dbo.fn_MaxInt(@Num1 INT, @Num2 INT)
RETURNS INT
AS
--Helper function
--Return the larger of two integers
BEGIN
	RETURN CASE WHEN @Num1 > @Num2 THEN @Num1 ELSE @Num2 END
END

Now, the one we really want:

CREATE FUNCTION dbo.fn_WeekdayDiff(@StartDate DATETIME, @EndDate DATETIME)
RETURNS INT
AS
--Calculdate weekdays between two dates
BEGIN
	--if @StartDate is AFTER @EndDate, swap them
	IF @StartDate > @EndDate
	BEGIN
		DECLARE @TempDate DATETIME
		SET @TempDate = @StartDate
		SET @StartDate = @EndDate
		SET @EndDate = @TempDate
	END
 
	RETURN
		--number of weeks x 5 weekdays/week
		  (DATEDIFF(ww, @StartDate, @EndDate) * 5)
		--add weekdays left in current week
		+ CASE DATEPART(dw, @StartDate + @@DATEFIRST) WHEN 1 THEN 5 ELSE (7 - DATEPART(dw, @StartDate + @@DATEFIRST)) END
		--subtract weekdays after @EndDate
		- dbo.fn_MaxInt(6 - DATEPART(dw, @EndDate + @@DATEFIRST), 0)
END

Finally, some test cases:

SELECT
	  dbo.fn_WeekDayDiff('2008-11-04', '2008-12-25') [2008-11-04:2008-12-25]
	, dbo.fn_WeekDayDiff('2008-11-04', '2008-12-26') [2008-11-04:2008-12-26]
	, dbo.fn_WeekDayDiff('2008-11-04', '2008-12-27') [2008-11-04:2008-12-27]
	, dbo.fn_WeekDayDiff('2008-11-04', '2008-12-28') [2008-11-04:2008-12-28]
	, dbo.fn_WeekDayDiff('2008-11-04', '2008-12-29') [2008-11-04:2008-12-29]
	, dbo.fn_WeekDayDiff('2008-11-04', '2008-12-30') [2008-11-04:2008-12-30]
	, dbo.fn_WeekDayDiff('2008-11-04', '2008-11-06') [2008-11-04:2008-11-06]
	, dbo.fn_WeekDayDiff('2008-11-04', '2008-11-04') [2008-11-04:2008-11-04]
	, dbo.fn_WeekDayDiff('2008-11-06', '2008-11-04') [2008-11-06:2008-11-04]

And results:

2008-11-04:2008-12-25 2008-11-04:2008-12-26 2008-11-04:2008-12-27 2008-11-04:2008-12-28 2008-11-04:2008-12-29 2008-11-04:2008-12-30 2008-11-04:2008-11-06 2008-11-04:2008-11-04 2008-11-06:2008-11-04
--------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- ---------------------
38                    39                    39                    39                    40                    41                    3                     1                     3

Tags: date calculation, DATEDIFF, DATEFIRST, DATEPART, HOWTO, SQL Server

2 Responses to Calculate Weekdays Between Two Dates

Todd

April 6th, 2009 at 10:18 am

This doesn’t seem work if the @startdate is a Sunday.

for ex:

select dbo.fn_WeekdayDiff(’2009-03-22′, ’2009-03-22′)
— should return 0, returns 1

select dbo.fn_WeekdayDiff(’2009-03-22′, ’2009-04-07′)
— should return 12, returns 13

Taylor Gerring

April 13th, 2009 at 11:59 am

Hrm, interesting bug. I’ve updated the code to correct this particular condition.

Idea Excursion

Calculate Weekdays Between Two Dates

2 Responses to Calculate Weekdays Between Two Dates

Comment Form

Categories

Archives

Blogroll

Other