Click here to Skip to main content
14,975,683 members
Articles / Database Development / SQL Server
Posted 9 Jun 2005


59 bookmarked

Optimized Calculation Algorithm for Business Days

Rate me:
Please Sign up or sign in to vote.
3.90/5 (18 votes)
7 Sep 20055 min read
Library that implements business days and age calculation and also implements the DateDiff function in C#.


My teammate came to me and asked, how to figure out the number of business days between two given dates? My immediate reply was, loop through start date to end date. But after some time, I realized that this solution is not optimized. So I started developing my own optimized algorithm.

In this article we will discuss the algorithm and see the implementation on SQL Server 2000 and also some C# code.

Let’s start with algorithms first. Here are the key factors that are involved in the calculation.

The factors

  1. How many week days per week?

    This depends on the company/country policies. In the US, it's generally 5 days per week, whereas in India and in many other countries, many companies work for 6 days a week. Our algorithm needs to take that into consideration.

  2. How many holidays?

    The second factor is the number of holidays during a specified period. Again that depends on company policy. We can maintain these details in one table. That is simple too.

Basic algorithm

  1. Calculate the number of time span in terms of weeks. Call it, W.
  2. Deduct the first week from the number of weeks. W= W-1
  3. Multiply the number of weeks with the number of working days per week. Call it, D.
  4. Find out the holidays during the specified time span. Call it, H.
  5. Calculate the days in the first week. Call it, SD.
  6. Calculate the days in the last week. Call it, ED.
  7. Sum up all the days. BD = D + SD + ED – H.

Simple enough hum…!!!

As we know the number of working days per week, and as the pattern repeats every week, first we will calculate the number of complete weeks. We should deduct one from the number of weeks. Once we get the number of weeks, we can then multiply by the number of working days and arrive at a rough number of business days.

OK, so we have a rough number of business days, say D. I am calling it ruff number as it is not the exact answer, as we have deducted one week in the first step.

Now, we will find out how many days are there in the starting week. For example if the starting day is Wednesday, the number of days in the starting week is 3 (if number of business days in a week is 5) or 4 (if number of business days in a week is 6).

Similarly, find out how many days are there in the last week. At last, find out how many holidays fall during that time span. That will be a simple aggregation query on the Holiday table.

Once we have all the numbers in hand, based on the mentioned expression, we can come up with our desired answer. Hope this clears all your doubts. So let’s start implementing this algorithm.

First, we will try to implement this on SQL Server using T-SQL.

SQL Server implementation

SQL Server has a number of date functions, two of them are DATEDIFF and DATEPART. That will come handy in this implementation.

You can implement this algorithm as a procedure or a function. The preferred one is function but here I will implement this as a procedure so that we can use the print command. Once everything is OK, you can convert it to a procedure by just removing the print command and using return.

The solution

Create procedure SpBusinessDays (@dtStartDate datetime, @dtEndDate datetime,
@indDaysInWeek int)
    @intWeeks int
    ,@indDays int 
    ,@intSdays int
    ,@intEdays int
    -- Find the number of weeks between the dates. Subtract 1 
    -- since we do not want to count the current week.
    select @intWeeks = datediff( week, @dtStartDate, @dtEndDate) - 1
    print 'week'
    print @intWeeks
    -- calculate the number of days in these compelete weeks.
    select @indDays = @intWeeks * @indDaysInWeek
    print 'Est. Days'
    print @indDays
    -- Get the number of days in the starting week. 
    if @indDaysInWeek = 5

        -- If Saturday, Sunday is holiday
        if datepart( dw, @dtStartDate) = 7
            select @intSdays = 7 - datepart( dw, @dtStartDate)
            select @intSdays = 7 - datepart( dw, @dtStartDate) - 1
        -- If Sunday is only <st1:place>Holiday</st1:place>
        select @intSdays = 7 - datepart( dw, @dtStartDate) 
    print 'Starting Days'
    print @intSdays
    -- Calculate the days in the last week. 
    if @indDaysInWeek = 5
        if datepart( dw, @dtEndDate) = 7
            select @intEdays = datepart( dw, @dtEndDate) - 2
            select @intEdays = datepart( dw, @dtEndDate) - 1
        select @intEdays = datepart( dw, @dtEndDate) - 1
    print 'End Days' 
    print @intEdays
    -- Sum everything together.
    select @indDays = @indDays + @intSdays + @intEdays
    print 'Ans'
    print @indDays

Note: Starting date is Exclusive.

Here if you notice, if the number of working days is 6, we need not worry about any thing, we can simply count the days. If the number of working days is 5 then we have to take care of Saturday.

That’s it. It’s simple.

C# implementation

OK, but let’s say if you calculate this on your presentation layer, the stored procedure or “function” will not work. So now we will implement the same algorithm in C#.

Here is the implementation in C#:

/// <summary>
/// Calulates Business Days within the given range of days.
/// Start date and End date inclusive.
/// </summary>
/// <param name="startDate">Datetime object 
/// containing Starting Date</param>
/// <param name="EndDate">Datetime object containing 
/// End Date</param>
/// <param name="NoOfDayWeek">integer denoting No of Business 
/// Day in a week</param>
/// <param name="DayType"> DayType=0 for Business Day and 
/// DayType=1 for WeekEnds </param>
/// <returns></returns>

public static double CalculateBDay(
    DateTime startDate, 
    DateTime EndDate, 
    int NoOfDayWeek, /* No of Working Day per week*/
    int DayType 
    double iWeek, iDays, isDays, ieDays;
    //* Find the number of weeks between the dates. Subtract 1 */
    // since we do not want to count the current week. * /
    iWeek =DateDiff("ww",startDate,EndDate)-1 ;
    iDays = iWeek * NoOfDayWeek;
    if( NoOfDayWeek == 5)
        //-- If Saturday, Sunday is holiday
        if ( startDate.DayOfWeek == DayOfWeek.Saturday ) 
            isDays = 7 -(int) startDate.DayOfWeek;
            isDays = 7 - (int)startDate.DayOfWeek - 1;
        //-- If Sunday is only <st1:place>Holiday</st1:place>
        isDays = 7 - (int)startDate.DayOfWeek;
    //-- Calculate the days in the last week. These are not included in the
    //-- week calculation. Since we are starting with the end date, we only
    //-- remove the Sunday (datepart=1) from the number of days. If the end
    //-- date is Saturday, correct for this.
    if( NoOfDayWeek == 5)
        if( EndDate.DayOfWeek == DayOfWeek.Saturday ) 
            ieDays = (int)EndDate.DayOfWeek - 2;
            ieDays = (int)EndDate.DayOfWeek - 1;
        ieDays = (int)EndDate.DayOfWeek - 1 ;
    //-- Sum everything together.
    iDays = iDays + isDays + ieDays;
    if(DayType ==0) 
        return iDays;
        return T.Days - iDays; 

By products

  • DateDiff function

    While I was working on this issue, I also came across another issue with DateTime functions in C#. I came to know that C# does not have important functions like DateDiff, found in VB.NET. So I have included that in the same library. As Tim McCurdy said we can include Microsoft.VisualBasic.dll to our project and use DateDiff function implemented by the VB team, but I have noticed many people don't like the idea of mixing C# code with VB.NET code, though it is technically perfectly fine. Second problem with calculation of week, month or year is, they are not simple. You can not get the number of weeks = TimeSpan.Totaldays / 7. The rule says, number of week equals to number of time you cross the week boundary for given duration. To solve this problem I have added a new function called GetWeeks.

    /// <summary>
    /// Calculate weeks between starting date and ending date
    /// </summary>
    /// <param name="stdate"></param>
    /// <param name="eddate"></param>
    /// <returns></returns>
    public static int GetWeeks(DateTime stdate, DateTime eddate )
      TimeSpan t= eddate - stdate;
      int iDays;
      if( t.Days < 7)
        if(stdate.DayOfWeek > eddate.DayOfWeek)
          return 1; //It is accross the week 
          return 0; // same week
        iDays = t.Days -7 +(int) stdate.DayOfWeek ;
        int i=0;
        int k=0;
        for(i=1;k<iDays ;i++)
        if(i>1 && eddate.DayOfWeek != DayOfWeek.Sunday ) i-=1; 
          return i;
    /// <summary>
    /// Mimic the Implementation of DateDiff function of VB.Net.
    /// Note : Number of Year/Month is calculated
    ///        as how many times you have crossed the boundry.
    /// e.g. if you say starting date is 29/01/2005
    ///        and 01/02/2005 the year will be 0,month will be 1.
    /// </summary>
    /// <param name="datePart">specifies on which part 
    ///   of the date to calculate the difference </param>
    /// <param name="startDate">Datetime object containing
    ///   the beginning date for the calculation</param>
    /// <param name="endDate">Datetime object containing
    ///   the ending date for the calculation</param>
    /// <returns></returns>
    public static double DateDiff(string datePart, 
                  DateTime startDate, DateTime endDate)
      //Get the difference in terms of TimeSpan
      TimeSpan T;
      T = endDate - startDate;
      //Get the difference in terms of Month and Year.
      int sMonth, eMonth, sYear, eYear;
      sMonth = startDate.Month;
      eMonth = endDate.Month;
      sYear = startDate.Year;
      eYear = endDate.Year; 
      double Months,Years=0;
      Months = eMonth - sMonth;
      Years = eYear - sYear;
      Months = Months + ( Years*12);
        case "WW":
        case "DW":
             return (double)GetWeeks(startDate,endDate);
        case "MM":
             return Months;
        case "YY":
        case "YYYY":
             return Years;
        case "QQ":
        case "QQQQ":
             //Difference in Terms of Quater
             return Math.Ceiling((double)T.Days/90.0);
        case "MI":
        case "N":
             return T.TotalMinutes ;
        case "HH":
             return T.TotalHours ;
        case "SS":
             return T.TotalSeconds;
        case "MS":
             return T.TotalMilliseconds;
        case "DD":
             return T.Days; 
  • Age calculation

    This is a simple calculation compared to business days. I have added the function that can calculate the age on a specified date, in terms of year, month and days:

    /// <summary>
    /// Calculate Age on given date.
    /// Calculates as Years, Months and Days.
    /// </summary>
    /// <param name="DOB">Datetime object 
    /// containing DOB value</param>
    /// <param name="OnDate">Datetime object containing given 
    /// date, for which we need to calculate the age</param>
    /// <returns></returns>
    public static string Age(DateTime DOB, DateTime OnDate)
        //Get the difference in terms of Month and Year.
        int sMonth, eMonth, sYear, eYear;
        double Months, Years;
        sMonth = DOB.Month;
        eMonth = OnDate.Month;
        sYear = DOB.Year;
        eYear = OnDate.Year; 
        // calculate Year
        if( eMonth >= sMonth) 
            Years = eYear - sYear;
            Years = eYear - sYear -1;
        //calculate Months
        if( eMonth >= sMonth) 
            Months = eMonth - sMonth;
            if ( OnDate.Day > DOB.Day)
                Months = (12-sMonth)+eMonth-1;
                Months = (12-sMonth)+eMonth-2;
        double tDays=0;
        //calculate Days
        if( eMonth != sMonth && OnDate.Day != DOB.Day ) 
            if(OnDate.Day > DOB.Day) 
            tDays = DateTime.DaysInMonth(OnDate.Year, 
                                       OnDate.Month) - DOB.Day; 
            tDays = DateTime.DaysInMonth(OnDate.Year, 
                        OnDate.Month-1) - DOB.Day + OnDate.Day ; 
        string strAge = Years+"/"+Months+"/"+tDays; 
        return strAge;


If you notice the algorithm, I have talked about holidays too, but I have not implemented this in any of the above code but that can be taken care of by a simple query. So I am leaving that up to you. Do let me know if you like this code, or if it was useful in your project. You can mail me at


  • Bug fixed for calculation of weeks for given duration in C# code. 17th Aug, 2005.
  • First release on 10th Jun, 2005.


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Gaurang Desai
United States United States
Currently he is working as Senior Soft. Engineer at Cognizant Technology Solution.He is involved in various project activities like System Architecture, Design, and Development. He is fond of conduction training for various technologies. He has have worked on various language and platforms. He is Microsoft and Oracle Certified professional. He is spending quantity and quality time in .Net world. He had also spoiled his hand with java, too.
If work is not demanding, he spends good time with his wife, Purvi.He

Comments and Discussions

SuggestionDoing maths Pin
Go Cova6-Oct-11 8:25
MemberGo Cova6-Oct-11 8:25 
GeneralFor SQL bug Pin
Terry Lo10-Sep-10 0:18
MemberTerry Lo10-Sep-10 0:18 
GeneralThat algorithm is optimized even better: Pin
Alec Pojidaev30-Oct-09 11:19
MemberAlec Pojidaev30-Oct-09 11:19 
GeneralSimpler Algorithm Pin
Patrick Nikoletich13-Apr-09 8:56
MemberPatrick Nikoletich13-Apr-09 8:56 
Questionnew updates ?? Pin
kiquenet.com9-Dec-08 23:52
professionalkiquenet.com9-Dec-08 23:52 
QuestionI think this is a simpler solution...? Pin
purplepiano4-Jul-07 0:51
Memberpurplepiano4-Jul-07 0:51 
AnswerRe: I think this is a simpler solution...? Pin
kiquenet.com11-Dec-08 1:19
professionalkiquenet.com11-Dec-08 1:19 
Generalnew code Pin
xyjuan20-Apr-07 9:37
Memberxyjuan20-Apr-07 9:37 
QuestionWeeks between two dates Pin
ThePhred30-Sep-05 11:29
MemberThePhred30-Sep-05 11:29 
NewsRe: Weeks between two dates Pin
ThePhred30-Sep-05 11:33
MemberThePhred30-Sep-05 11:33 
GeneralOPTIMIZED for business days Pin
seeblunt13-Sep-05 1:30
Memberseeblunt13-Sep-05 1:30 
GeneralRe: OPTIMIZED for business days Pin
Juneau12-May-06 8:23
MemberJuneau12-May-06 8:23 
QuestionNon Sat/Sun weekends? Pin
Keith Farmer7-Sep-05 10:40
MemberKeith Farmer7-Sep-05 10:40 
AnswerRe: Non Sat/Sun weekends? Pin
Anonymous9-Sep-05 8:10
MemberAnonymous9-Sep-05 8:10 
QuestionNo DateDiff? Pin
Tim McCurdy15-Aug-05 17:33
MemberTim McCurdy15-Aug-05 17:33 
AnswerRe: No DateDiff? Pin
Gaurang Desai16-Aug-05 1:51
MemberGaurang Desai16-Aug-05 1:51 
GeneralRe: No DateDiff? Pin
Tim McCurdy16-Aug-05 5:19
MemberTim McCurdy16-Aug-05 5:19 
GeneralRe: No DateDiff? Pin
Gaurang Desai16-Aug-05 18:09
MemberGaurang Desai16-Aug-05 18:09 
GeneralRe: No DateDiff? Pin
Tim McCurdy16-Aug-05 5:28
MemberTim McCurdy16-Aug-05 5:28 
GeneralRe: No DateDiff? Pin
Gaurang Desai16-Aug-05 18:12
MemberGaurang Desai16-Aug-05 18:12 
GeneralRe: No DateDiff? Pin
SiPurdy7-Sep-05 18:54
MemberSiPurdy7-Sep-05 18:54 
AnswerRe: No DateDiff? Pin
Tim McCurdy17-Aug-05 2:40
MemberTim McCurdy17-Aug-05 2:40 
GeneralLast POsting Lost Something In Translation ! Pin
Nick Lucas2-Aug-05 22:42
MemberNick Lucas2-Aug-05 22:42 
GeneralJuly 1st To July 27th Pin
Nick Lucas2-Aug-05 22:28
MemberNick Lucas2-Aug-05 22:28 
GeneralRe: July 1st To July 27th Pin
Gaurang Desai4-Aug-05 2:49
MemberGaurang Desai4-Aug-05 2:49 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.