Function to recognize the 5-day work week and U.S. holidays

How do I change the function to get the next, two days, three days, five days or tens days from the inputDate and that is not in the holidays table and not a weekend ??

create function getNextWorkDay (@inputDate datetime, @numOfDays int)
  returns datetime
as
begin
  declare @nextday datetime;
  set @nextday = dateadd(d,@numOfDays,@inputdate);
 
  while (datepart(weekday,@nextday) = 1 or datepart(weekday,@nextday) = 7 or
     exists (select 1 from hols_table where hol_date = @nextday))
  begin
    set @nextday = dateadd(d,@numOfDays,@nextday);
  end;
  return @nextday
end;

But if in the part of the code be "set @nextday = dateadd(d,@numOfDays,@nextday);" wouldn't always add more than the days wanted. For example if I pass 3 days it will always add 3 days more and not add one day more after the next three days that is not holiday or weekend. Shouldn't be this way: ??

create function getNextWorkDay (@inputDate datetime, @numOfDays int)
  returns datetime
as
begin
  declare @nextday datetime;
  set @nextday = dateadd(d,@numOfDays,@inputdate);
 
  while (datepart(weekday,@nextday) = 1 or datepart(weekday,@nextday) = 7 or
     exists (select 1 from hols_table where hol_date = @nextday))
  begin
    set @nextday = dateadd(d,1,@nextday);
  end;
  return @nextday
end;

Or did you meant to jump n working days
create function getNextWorkDay (@inputDate datetime, @numOfDays int)
  returns datetime
as
begin
  declare @nextday datetime;
  declare @calcday int;
  set @nextday = dateadd(d,1,@inputdate);
  set @calcday = 0;
  while (datepart(weekday,@nextday) = 1 or datepart(weekday,@nextday) = 7 or
     exists (select 1 from hols_table where hol_date = @nextday) or (@calcday < @numOfDays))
  begin
       if not ((datepart(weekday,@nextday) = 1 or datepart(weekday,@nextday) = 7) then
          set @calcday = @calcday + 1;
       end if;
    set @nextday = dateadd(d,1,@nextday);
  end;
  return @nextday
end;

Your fix is right! sorry!
The first option is: The first working day next n day from now
The second option is : The date of the 10th working day

On my second option I forgot to add the "or
     exists (select 1 from hols_table where hol_date = @nextday)"

create function getNextWorkDay (@inputDate datetime, @numOfDays int)
  returns datetime
as
begin
  declare @nextday datetime;
  declare @calcday int;
  set @nextday = dateadd(d,1,@inputdate);
  set @calcday = 0;
  while (datepart(weekday,@nextday) = 1 or datepart(weekday,@nextday) = 7 or
     exists (select 1 from hols_table where hol_date = @nextday) or (@calcday < @numOfDays))
  begin
       if not ((datepart(weekday,@nextday) = 1 or datepart(weekday,@nextday) = 7) or
     exists (select 1 from hols_table where hol_date = @nextday) then
          set @calcday = @calcday + 1;
       end if;
    set @nextday = dateadd(d,1,@nextday);
  end;
  return @nextday
end;

gkern your script is giving me the below syntax error:

Msg 156, Level 15, State 1, Procedure getNextWorkDay, Line 13
Incorrect syntax near the keyword 'then'.
Msg 170, Level 15, State 1, Procedure getNextWorkDay, Line 18
Line 18: Incorrect syntax near ';'.

check this one

create function getNextWorkDay (@inputDate datetime, @numOfDays int)
  returns datetime
as
begin
  declare @nextday datetime;
  declare @calcday int;
  set @nextday = dateadd(d,1,@inputdate);
  set @calcday = 0;
  while (datepart(weekday,@nextday) = 1 or datepart(weekday,@nextday) = 7 or
     exists (select 1 from hols_table where hol_date = @nextday) or (@calcday < @numOfDays))
  begin
       if not exists (select case datepart(weekday,@nextday) when 1 then 1 when 7 then 1 else null end) 
              or exists (select 1 from hols_table where hol_date = @nextday) 
              or exists (select case when @calcday < @numOfDays then 1 else null end) 
          set @calcday = @calcday + 1;
    set @nextday = dateadd(d,1,@nextday);
  end;
  return @nextday
end;

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:

Would'nt the script below do the same thing as the above? Once this one below will execute faster. If not what's the difference?

create function getNextWorkDay (@inputDate datetime, @numOfDays int)
  returns datetime
as
begin
  declare @nextday datetime;
  set @nextday = dateadd(d,@numOfDays,@inputdate);
 
  while (datepart(weekday,@nextday) = 1 or datepart(weekday,@nextday) = 7 or
     exists (select 1 from hols_table where hol_date = @nextday))
  begin
    set @nextday = dateadd(d,1,@nextday);
  end;
  return @nextday
end;

Yes, this one would do the same, and its clearer what its doing.

Ok one last comment/question. In my holidays table the column "holiday_date" has the holiday's date which also includes the year in it. In the function when it compares "exists (select 1 from hols_table where hol_date = @nextday)" it will never be a holiday if the year doesn't match. Am I right ? If so how can I change it to work correctly?

So I can close this question I just need to figure out why is the function not working. One thing I noticed is that in my holidays table the time will always be "00:00:00.000" so in the function when it compares the datetime with the one in my holidays table it will have some time in it. Because of this it's not comparing correctly at "(select 1 from hols_table where hol_date = @nextday)". How do I change to compare only the date ??

Not quite sure what your are asking in the "it will never be a holiday if the year doesn't match" post. Some holidays are moveable, don't fall on the same date from year to year, eg Easter, so it is meaningless to check the date without a year part.

if your input date value has a time component, you can  eliminate the time part using:

  set @nextday = cast(cast(@inputdate as int) as datetime);
  set @nextday = dateadd(d,@numOfDays,@nextday);
                                              

1:
2:
3:

Noth without the year part I meant without time part.

With all the information I came up with the script below. But I found that when adding one day to the date it's also counting the weekend and holiday. For example if I pass ('2009-02-11 17:10:00.000', 7) it will return me the date "2009-02-18 00:00:00.000" but the correct counting ONLY bussiness day it has to return "2009-02-20 00:00:00.000". Can anyone help me to correct the script ??

CREATE FUNCTION dbo.NextWorkDay (@inputDate DATETIME, @numOfDays INT)
  RETURNS DATETIME
AS
BEGIN
 
DECLARE @nextday DATETIME;
DECLARE @calcday INT;
DECLARE @hour INT;
 
SET @hour = DATEPART(hour,@inputdate);
 
--If the order was placed after 6:00PM start counting one day after
IF(@hour >= 18) BEGIN
	SET @nextday = DATEADD(d,1,@inputdate);
END
ELSE
	SET @nextday = @inputdate;
 
--To set the time to zeros to compare the dates
SET @nextday = (SELECT DATEADD(dd,0, DATEDIFF(dd,0,@nextday)));
 
SET @calcday = 0;
 
WHILE (DATEPART(weekday,@nextday) = 1 or DATEPART(weekday,@nextday) = 7 or
     exists (SELECT 1 FROM hols_table WHERE holiday = @nextday) or (@calcday < @numOfDays))
  BEGIN
       IF NOT EXISTS (SELECT CASE DATEPART(weekday,@nextday) WHEN 1 THEN 1 WHEN 7 THEN 1 ELSE NULL END) 
              OR EXISTS (SELECT 1 FROM hols_table WHERE holiday = @nextday) 
              OR EXISTS (SELECT CASE WHEN @calcday < @numOfDays THEN 1 ELSE NULL END) 
          SET @calcday = @calcday + 1;
    SET @nextday = DATEADD(d,1,@nextday);
  END;
 
RETURN @nextday
END

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:

How about setting the IF to the following.

This should only increment the days count when it is not a weekend and not a holiday date

    if datepart(weekday,@nextday) != 1 and datepart(weekday,@nextday) != 7 and
        not exists (select 1 from hols_table where holiday = @nextday) 
      set @calcday = @calcday + 1;
                                              

1:
2:
3:

Where in the script I pasted should I change ? Can you modify my script and paste it here ?

Can anyone help with this ?