Add / Subtracting days from a date to get the next date without counting weekend

Recently had a situation where I had to calculate the next date based on the duration provided to the function. The catch was not to count the weekend (Saturday and Sunday) in the calculation.

I did a lot of google search and landed with  a way to get the next date if the duration was positive (that is the next date will be later than the provided date). I needed a way to go back as well.

After a lot of learning, I came up with this T SQL function for SQL Server that will allow you pass a positive or negative duration parameter and a date. Based on the parameter, it will add or subtract numbers.

Here is the T SQL function. Have fun and enjoy. Feel free to use this, I would really appreciate acknowledgement and link to my blog (http://dmknol.blogspot.com/)

create function [dbo].[GetEndDate_ByWeekdays]
(
 @pdtStartDate datetime,
 @piDuration int
)
returns datetime
as
begin
 declare @rdt datetime
 -- calculating forward
 if (@piDuration > 0)
 begin
   select @rdt = dateadd (d, case datepart (dw, @pdtStartDate) when 7 then 2 when 1 then 1 else 0 end, @pdtStartDate)
   + (datepart (dw, dateadd (d, case datepart (dw, @pdtStartDate) when 7 then 2 when 1 then 1 else 0 end,
    @pdtStartDate)) - 2 + @piDuration) % 5
   + ((datepart (dw, dateadd (d, case datepart (dw, @pdtStartDate) when 7 then 2 when 1 then 1 else 0 end,
    @pdtStartDate)) - 2 + @piDuration) / 5) * 7
   - (datepart (dw, dateadd (d, case datepart (dw, @pdtStartDate) when 7 then 2 when 1 then 1 else 0 end,
   @pdtStartDate)) - 2)
 end

 -- calculating backward
 else
 begin
  -- local variable
  declare @liDuration int,
    @ldt_orgStart datetime,
    @li_orgDuration int
   
  -- save original value
  set @ldt_orgStart = @pdtStartDate
  set @li_orgDuration = @piDuration
  -- divide the duration into set of fives
  while abs(@piDuration)/5 > 0
   begin
    set @piDuration = @piDuration + 5
    --select [@piduration] = @piDuration
    set @liDuration = 7 - 2*(datepart (dw, @pdtStartDate) /7) + (datepart (dw, @pdtStartDate) -2)/7
    set @liduration = @liDuration *(-1)
    --select [@liduration] = @liDuration
    set @pdtStartDate = dateadd(d, @liDuration, @pdtStartDate)
    --select [@pdtStartDate_inside] = @pdtStartDate
   end
  -- add days that were not covered within the five sets
  if (abs(@li_orgDuration) % 5 > 0)
  begin
   --select @piDuration, @pdtStartDate, datepart (dw, @pdtStartDate)
   set @liDuration = case when datepart (dw, @pdtStartDate) <= abs(@piDuration) + 1 then abs(@piDuration) + 2
         when datepart (dw, @pdtStartDate) = 1 then abs(@piDuration) + 1
         else abs(@piDuration) end
   set @liDuration = @liDuration * (-1)
   --select [@liDuration_last] = @liDuration
   set @rdt = dateadd(d, @liDuration, @pdtStartDate)
  end
  else
   set @rdt = @pdtStartDate
 end

 -- return the date
 return @rdt
end
GO