SQL Server Function to add working days on to a date

RoddyCrossan — Fri, 21 Aug 2009 09:57:05 GMT

Originally posted on: http://geekswithblogs.net/RoddyCrossan/archive/2009/08/21/sql-server-function-to-add-working-days-on-to-a.aspx

This is my first post so please be nice to me… I have recently been asked to look at automating the recalculation of the SLA target on helpdesk calls which had the clock stopped prior to closure. When I had my first look it seemed nice and simple… you have a number of days to add to the target date so all I would need is a DATEADD() function and kablamo something that was taking a person a day a month today could be done in a matter of seconds.

However when I went into a bit more detail however it appears that what they wanted was to add on only working days, e.g. if the target goal is 2 days and the target date is on a Friday this should actually recalculate the target date as Tuesday. So this raised the question what is the simplest way to add 2 working days to a date.

Now I am not saying that my solution is the most elegant solution (generally I try and avoid using functions if I can) in the world but it certainly seemed to work for me… what I decided to do was create a function which would do the work for me, the reason for doing it this way was partially for speed and partially as it could be a fairly useful tool for other things.

Below is the SQL used to create the function :

CREATE FUNCTION AddWorkDays 
(    
    @WorkingDays As Int, 
    @StartDate AS DateTime 
) 
RETURNS DateTime 
AS 
BEGIN 
    DECLARE @Count AS Int 
    DECLARE @i As Int 
    DECLARE @NewDate As DateTime 
    SET @Count = 0 
    SET @i = 0 

    WHILE (@i < @WorkingDays) --runs through the number of days to add 
    BEGIN 
-- increments the count variable 
        SELECT @Count = @Count + 1 
-- increments the i variable 
        SELECT @i = @i + 1 
-- adds the count on to the StartDate and checks if this new date is a Saturday or Sunday 
-- if it is a Saturday or Sunday it enters the nested while loop and increments the count variable 
           WHILE DATEPART(weekday,DATEADD(d, @Count, @StartDate)) IN (1,7) 
            BEGIN 
                SELECT @Count = @Count + 1 
            END 
    END 

-- adds the eventual count on to the Start Date and returns the new date 
    SELECT @NewDate = DATEADD(d,@Count,@StartDate) 
    RETURN @NewDate 
END 
GO

Hope it is helpful, it got me by at least :)

Roddy Crossan

SQL Server Function to add working days on to a date