SQL Server User Defined Function for Business Days Calculation

SQL Server - UDF for Business Days Calculation

By Robbe D. Morris

Printer Friendly Version

Robbe & Melisa Morris

Here's a pair of user-defined functions designed to calculate the number of business days that exists between a particular date range. The first User-Defined Function is called dbo.IsDateAHoliday and simply compares the passed in date with a list of holidays for a specific year. You may opt to put this in a table especially if you plan on using the same set of holidays across many databases. The second User-Defined Function dbo.GetBusinessDays is what we'll call from our select statements and will return the actual number of business days within the passed in date range.

In order to utilize these two user-defined functions, copy and paste them into new user-defined functions and set the appropriate permissions. Then, just open SQL Server Query Analyzer and try them out with the test code below.

dbo.IsDateAHoldiay

  
CREATE function dbo.IsDateAHoliday
(
     @CurDate datetime
) 
returns int
 as
begin

  declare @ret int
  
  select @ret = 0
 
 select @ret = case
    when datepart(yy,@CurDate) = 2004 and datepart(mm,@CurDate) = 1 and datepart(dd,@CurDate) = 1 then 1
    when datepart(yy,@CurDate) = 2004 and datepart(mm,@CurDate) = 7 and datepart(dd,@CurDate) = 4 then 1
    when datepart(yy,@CurDate) = 2004 and datepart(mm,@CurDate) = 9 and datepart(dd,@CurDate) = 1 then 1
    when datepart(yy,@CurDate) = 2004 and datepart(mm,@CurDate) = 11 and datepart(dd,@CurDate) = 22 then 1
    when datepart(yy,@CurDate) = 2004 and datepart(mm,@CurDate) = 12 and datepart(dd,@CurDate) = 24 then 1
    when datepart(yy,@CurDate) = 2004 and datepart(mm,@CurDate) = 12 and datepart(dd,@CurDate) = 25 then 1
    when datepart(yy,@CurDate) = 2003 and datepart(mm,@CurDate) = 1 and datepart(dd,@CurDate) = 1 then 1
    when datepart(yy,@CurDate) = 2003 and datepart(mm,@CurDate) = 7 and datepart(dd,@CurDate) = 4 then 1
    when datepart(yy,@CurDate) = 2003 and datepart(mm,@CurDate) = 9 and datepart(dd,@CurDate) = 1 then 1
    when datepart(yy,@CurDate) = 2003 and datepart(mm,@CurDate) = 11 and datepart(dd,@CurDate) = 22 then 1
    when datepart(yy,@CurDate) = 2003 and datepart(mm,@CurDate) = 12 and datepart(dd,@CurDate) = 24 then 1
    when datepart(yy,@CurDate) = 2003 and datepart(mm,@CurDate) = 12 and datepart(dd,@CurDate) = 25 then 1
    else 0
  end
 
return @ret
end

dbo.GetBusinessDays

 
 CREATE function dbo.GetBusinessDays
(
     @StartDate datetime,
     @EndDate datetime
) 
returns int
 as
begin

  declare @DaysBetween int
  declare @BusinessDays int
  declare @Cnt int
  declare @EvalDate datetime

  select @DaysBetween = 0
  select @BusinessDays = 0
  select @Cnt=0

  select @DaysBetween = datediff(Day,@StartDate,@endDate) + 1  
   
   while @Cnt < @DaysBetween
     begin
  
            select @EvalDate = @StartDate + @Cnt
        
            if (dbo.IsDateAHoliday(@EvalDate)  = 0)
            BEGIN
                if ((datepart(dw,@EvalDate) <> 1) and (datepart(dw,@EvalDate) <> 7))
                    BEGIN
                         select @BusinessDays = @BusinessDays + 1
                    END
            END
      
          select @Cnt = @Cnt + 1
    end

 return @BusinessDays
end

Query Analyzer Test Code

 
 declare @StartDate datetime
 declare @EndDate datetime
 
 select @StartDate = cast('06/25/2003' as datetime)
 select @EndDate = cast('07/28/2003' as datetime)
   
 print cast(dbo.GetBusinessDays(@StartDate,@EndDate) as varchar(30))



	Robbe has been a Microsoft MVP in C# since 2004. He is also the co-founder of NullSkull.com which provides .NET articles, book reviews, software reviews, and software download and purchase advice.