Technical Article

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(
Casedatepart(weekday, dateadd(dd, SequenceID, @StartDate))
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

Share

Share

Rate

You rated this post out of 5. Change rating