Calculate Next Business Date Given Start Date And No. Of Days

Discussion in 'ASP' started by naimish, Aug 20, 2009.

  1. naimish

    naimish New Member

    Joined:
    Jun 29, 2009
    Messages:
    1,043
    Likes Received:
    18
    Trophy Points:
    0
    Occupation:
    Software Engineer
    Location:
    On Earth

    Introduction



    This stored procedure (works same in vb, c#) adds the given number of Business days to the given date. Only business days aare taken into account. Saturday and Sunday are not included.

    Background



    Find out how many weekends will come between start and end date and add that many extra days to the no. of days to the start date.

    (2 days for one weekend)

    1. Say you have to add 'N' business days days to start date ('stdate')
    2. Find out Working days left is a week call it 'wkdays'. (For ex if 'stdate' is tuesdays, then working days left = 3)
    3. Now we need to calculate how many weeks will come in between. This is done with a formula

      The formula is simple. weeks = ( N - 'wkdays' + 4) / 5

      First we subtract the days which are left in a week. ( N - wkdays). Now we are left with the no of days added.

      For this we add 4 to left days to take current week into account and then divide by 5 to see how many weekends will come as our week has only 5 business days.
    4. once we have no. of weekends in between we can simply calculate total no. of days ('tdays') to be added.

      tdays = N + (weeks*2)
    5. Once we have tdays we can add these n weeksends are taken into account automatically.

    P.S This code uses 2 fucntions

    1. DatePart - to calulate day of week
    2. DateAdd - to add the tdays in start date to get end date.

    The code



    Code:
    Create procedure SpAddBusinessDays1 (@dtStartDate datetime, @intadddays int)
    as
    begin
        declare 
        @intwkday int   --store day of week
            ,@intweeks int   --how many weeks in between
        ,@dtEndDate datetime  --To store end date
     
            if datepart(dw, @dtStartDate)= 7   --Converting saturday to sunday
                    select @dtStartDate = dateadd(d, 1, @dtStartDate)
            
            select @intwkday = 6 - datepart(dw, @dtStartDate)   --working days left in week
            select @intweeks = (@intadddays - @intwkday + 4) / 5   --No of weeks
            select @intadddays = (@intadddays + (@intweeks * 2))   --total days to add
            select @dtEndDate = dateadd(d, @intadddays, @dtStartDate)  -- end date
            
            print @dtEndDate
    end
    ---
    
     
  2. Saket

    Saket New Member

    Joined:
    Jul 21, 2009
    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    0
    Location:
    Don't Know
    Nice work mr. naimish
     
  3. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice