# Number of Weekdays between two dates - Set Based

,

Inspired by a post here at SQLServerCentral, I wrote this function to calculate the number of days between two dates that are weekdays. In order to achieve this I used a common SQL table that contains a sequence of number for 1 to X. This was used as an input to the datediff function, and the resulting date's "weekday" is checked to see if it was Saturday or Sunday (1 or 7). If so 0 otherwise 1, which is then summed up.

```/*----------------------------------------------------------------------

Supporting Table called Sequence.  This table is useful for a number of things.

*/----------------------------------------------------------------------

CREATE TABLE [Sequence] (
[SequenceID] [int] IDENTITY (1, 1) NOT NULL ,
PRIMARY KEY  CLUSTERED
(
[SequenceID]
)  ON [PRIMARY]
) ON [PRIMARY]
GO

declare @SequenceID Int

While isnull(@SequenceID, 0) <=1000

BEGIN

Insert Sequence default values

select @SequenceID = @@identity

END

/*----------------------------------------------------------------------

Function that calculates the number of work days between two dates.
The working days are considered the first and last day of every week

*/----------------------------------------------------------------------

create function UTIL_WorkingDaysDiff(@StartDate datetime, @EndDate datetime)

returns int

as

begin

declare @Count int

--check for null values
if @StartDate is null or @EndDate is null
goto fEND

---check that dates aren't the same
if convert(varchar(12), @StartDate, 101) = convert(varchar(12), @EndDate, 101)
BEGIN
Select @Count = 0
goto fEND
END

--check that startdate < endDate
if @StartDate > @EndDate
goto fEND

select @Count =
sum(
When 1 then 0--Sunday
When 7 then 0--Saturday
Else 1
END
)
From Sequence
where SequenceID <= datediff(dd, @StartDate, @EndDate)

fEND:

return @Count

END```

## Rate

You rated this post out of 5. Change rating

## Rate

You rated this post out of 5. Change rating