Adding and subtracting Business Days to a Date

Here is a short function that is designed to add/subtract business days to a date. The parameters to be passed are the number of business days to be added/subtracted (as int), and the date to be added/subtracted to (as datetime). The returned value is a datetime.   The function does not consider holidays as they are different among regions and companies. A holiday in other words is the same as a regular Mon-Friday working day.

SYNTAX
     businessDaysAdd(numbusinessdays, date)

Here is the complete function definition:

-- ============================================================================ -- Author: spartiatis1975@sqltipsandtricks.com -- Description: Function that adds or subtracts Business Days from a given date -- Business days are considered Mon - Fri. Holidays are not considered and are -- treated as work days. -- ============================================================================CREATE FUNCTION [dbo].[businessDaysAdd] ( @bDays int , -- number of business days to add sub. @d datetime -- variable to hold the date you want to add sub. days to) RETURNS datetime AS BEGIN   -- Declare the return variable here   DECLARE @nDate datetime, @addsub int   SET @ndate = @d   IF @bdays > 0     SET @addsub = 1   ELSE     SET @addsub = -1   WHILE @bdays <> 0 -- Keep adding/subtracting a day until @bdays becomes 0     BEGIN       SELECT @ndate = dateadd(day,1*@addsub,@ndate)       SELECT @bdays =       CASE datepart(weekday,@ndate)-- ignore if it is Sat or Sunday        WHEN 1 THEN @bdays         WHEN 7 THEN @bdays         ELSE @bdays - 1*@addsub       END     END RETURN @nDate END

numbusinessdays:
     This is the number of business days to be added (possitive int value) or subtracted (negative int value).
date:    The date you add or subtract business days to.

EXAMPLES:
    
    SELECT dbo.businessDaysAdd(-8,’8/17/09′)
    Output: 8/05/09

    SELECT dbo.businessDaysAdd(7,’8/17/09′)
    Output: 8/26/09

NOTE: Use of this function is ‘as is’ with no guarantees what so ever. You are responsible for your own testing.
Please share your comments/bugs/fixes with us.

Posted in: Adding and subtracting Business Days to a Date