SQL Server function to add working days to a date

Question

I have seen a number of postings regarding how to create an SQL Server function that will add a given number of working days to a date. But, none of them calculate exactly the way I need. We have a function that is currently done in code but that I want to move to a native SQL Server function (for use by stored procedures and in queries). We give our customers 5 working days to collect cargo before we start assessing storage charges. The 5 working days exclude weekends and holidays (we have a table with the holiday dates). The trick here is that I need to get the date immediately after the 5 working days regardless of whether it is a weekend or holiday. So I need the function to return the last working date, NOT the first working day after. So, for example:

Oct 20th (Sat) plus 5 working days = Oct 26th (Fri)
Oct 21st (Sun) plus 5 working days = Oct 26th (Fri)
Oct 22nd (Mon) plus 5 working days = Oct 29th (Mon)
May 19th (Sat) plus 5 working days with May 21st a holiday = May 28th

The 5 working days is the current allotment, but this may change in the future so the number of working days needs to be a parameter. Also, the function may be used over fairly large datasets so I would prefer doing this without loops. We are running SQL Server 2008.

Edit: This is not a duplicate of "Add business days to date in SQL without loops" as they want the ending date to be a working day. I want my ending date to be whatever date immediately follows the last grace day (ie: 5 working days Mon to Fri I want the Sat date returned, NOT the following Mon).

Answer 1

create table holidays (
  date date);
GO

create function dbo.findWorkDayAfter(@date datetime, @days int)
returns date as
begin
return (
  select thedate
  from (
  select thedate=dateadd(d,v.day,cast(@date as date)),
         rn=row_number() over (order by v.day)
  from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))v(day)
  left join holidays h on h.date = dateadd(d,v.day,cast(@date as date))
  where h.date is null and left(datename(dw,dateadd(d,v.day,cast(@date as date))),1) <> 'S'
  ) x
  where @days = rn
  )
end
GO

Unless you have long holidays, 10 days should be enough to find the 5th next working day. Increase it if you need to.

If you need a larger number of business days from a date, you can use this which will cater for a year or three.

alter function dbo.findWorkDayAfter(@date datetime, @days int)
returns date as
begin
return (
  select thedate
  from (
  select thedate=dateadd(d,v.number,cast(@date as date)),
         rn=row_number() over (order by v.number)
  from master..spt_values v
  left join holidays h on h.date = dateadd(d,v.number,cast(@date as date))
  where h.date is null and left(datename(dw,dateadd(d,v.number,cast(@date as date))),1) <> 'S'
    and v.number >= 1 and v.type='p'
  ) x
  where @days = rn
  )
end
GO

Answer 2

I had a similar requirement earlier so sharing following function which returns new date after adding given no of days (only working days ) to given date , it also gives option to exclude saturday from weekend.

ALTER FUNCTION [dbo].[AddDaysAndWeekends](
    @StartDate DATETIME,
    @NoOfDays INT,
    @IsSatrudayHoliday bit
)

RETURNS DATETIME AS BEGIN
    while (@NoOfDays>0)
    begin
        --add 1 day
        set @StartDate = DateAdd(day,1,@StartDate)
        --skip weekends
        while (DATEPART(dw, @StartDate) = 1 or (@IsSatrudayHoliday = 1 and DATEPART(dw, @StartDate) = 7))
        begin
            set @StartDate = DateAdd(day,1,@StartDate)
        end
        set @NoOfDays = @NoOfDays-1
    end
    --declare @dateadded int = DATEDIFF(day,@BaseDate,@StartDate)
    RETURN @StartDate
END

Note: if @StartDate falls in weekend, the function above does not consider changing @StartDate to next working date.

If there's a table with holidays declared like this:

CREATE TABLE Holiday(ID INT IDENTIFY, HolidayDate Date, ...)

Then Function consider changing @StartDate to next working date like this

ALTER FUNCTION [dbo].[AddDaysAndWeekends](
    @StartDate DATETIME,
    @NoOfDays INT,
    @IsSatrudayHoliday bit
)

RETURNS DATETIME AS BEGIN
    while (@NoOfDays>0)
    begin
        --add 1 day
        set @StartDate = DateAdd(day,1,@StartDate)
        --skip weekends
        while (
            DATEPART(dw, @StartDate) = 1 or
            (@IsSatrudayHoliday = 1 and DATEPART(dw, @StartDate) = 7) or
            exists (select 1 from Holiday where HolidayDate = convert(date, @StartDate))
        )
        begin
            set @StartDate = DateAdd(day,1,@StartDate)
        end
        set @NoOfDays = @NoOfDays-1
    end
    --declare @dateadded int = DATEDIFF(day,@BaseDate,@StartDate)
    RETURN @StartDate
END

Answer 3

All Credit to Bogdan Maxim & Peter Mortensen from Count work days between two dates . This is their post, I just added holidays to the function (This assumes you have a table "tblHolidays" with a datetime field "HolDate".) For newbies, there is a test script at the end. Happy Coding!

--Changing current database to the Master database allows function to be shared by everyone.
USE MASTER
GO
--If the function already exists, drop it.
IF EXISTS
(
    SELECT *
    FROM dbo.SYSOBJECTS
    WHERE ID = OBJECT_ID(N'[dbo].[fn_WorkDays]')
    AND XType IN (N'FN', N'IF', N'TF')
)

DROP FUNCTION [dbo].[fn_WorkDays]
GO
 CREATE FUNCTION dbo.fn_WorkDays
--Presets
--Define the input parameters (OK if reversed by mistake).
(
    @StartDate DATETIME,
    @EndDate   DATETIME = NULL --@EndDate replaced by @StartDate when DEFAULTed
)

--Define the output data type.
RETURNS INT

AS
--Calculate the RETURN of the function.
BEGIN
    --Declare local variables
    --Temporarily holds @EndDate during date reversal.
    DECLARE @Swap DATETIME

    --If the Start Date is null, return a NULL and exit.
    IF @StartDate IS NULL
        RETURN NULL

    --If the End Date is null, populate with Start Date value so will have two dates (required by DATEDIFF below).
    IF @EndDate IS NULL
        SELECT @EndDate = @StartDate

    --Strip the time element from both dates (just to be safe) by converting to whole days and back to a date.
    --Usually faster than CONVERT.
    --0 is a date (01/01/1900 00:00:00.000)
    SELECT @StartDate = DATEADD(dd,DATEDIFF(dd,0,@StartDate), 0),
            @EndDate   = DATEADD(dd,DATEDIFF(dd,0,@EndDate)  , 0)

    --If the inputs are in the wrong order, reverse them.
    IF @StartDate > @EndDate
        SELECT @Swap      = @EndDate,
               @EndDate   = @StartDate,
               @StartDate = @Swap

    --Calculate and return the number of workdays using the input parameters.
    --This is the meat of the function.
    --This is really just one formula with a couple of parts that are listed on separate lines for documentation purposes.
    RETURN (
        SELECT
        --Start with total number of days including weekends
        (DATEDIFF(dd,@StartDate, @EndDate)+1)
        --Subtact 2 days for each full weekend
        -(DATEDIFF(wk,@StartDate, @EndDate)*2)
        --If StartDate is a Sunday, Subtract 1
        -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday'
            THEN 1
            ELSE 0
        END)
        --If EndDate is a Saturday, Subtract 1
        -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday'
            THEN 1
            ELSE 0
        END)
        --Subtract all holidays
        -(Select Count(*) from tblHolidays
          where HolDate between @StartDate and @EndDate )
        )
    END  
GO

/*
    -- Test Script
declare @EndDate datetime= dateadd(m,2,getdate())
print @EndDate
select  [Master].[dbo].[fn_WorkDays] (getdate(), @EndDate)
*/