How to skip Weekends in DATEADD

In many cases, DATEADD function might need to use skipping weekends (Saturday and Sunday). For example, you might need to find all the invoices that are pending more than 5 working days.

SELECT *
FROM dbo.Invoice
WHERE [DAYSADDNOWK](InvoiceCreatedDate,5) < GETDATE()

CREATE FUNCTION DAYSADDNOWK(@addDate AS DATE, @numDays AS INT)

RETURNS DATETIME

AS

BEGIN

    SET @addDate = DATEADD(d, @numDays, @addDate)

    IF DATENAME(DW, @addDate) = 'sunday'   SET @addDate = DATEADD(d, 1, @addDate)

    IF DATENAME(DW, @addDate) = 'saturday' SET @addDate = DATEADD(d, 2, @addDate)

  

    RETURN CAST(@addDate AS DATETIME)

END

GO

This function is work for up to add 7 days. To support more than 7 days, the following function is used.

CREATE FUNCTION DAYSADDNOWK(@addDate AS DATE, @numDays AS INT)

RETURNS DATETIME

AS

BEGIN

    WHILE @numDays>0

    BEGIN

       SET @addDate=DATEADD(d,1,@addDate)

       IF DATENAME(DW,@addDate)='saturday' SET@addDate=DATEADD(d,1,@addDate)

       IF DATENAME(DW,@addDate)='sunday' SET @addDate=DATEADD(d,1,@addDate)

  

       SET @numDays=@numDays-1

    END

  

    RETURN CAST(@addDate AS DATETIME)

END

GO

The above functions are taken from below link. Please read this article for more deatils

https://social.technet.microsoft.com/wiki/contents/articles/30877.t-sql-extending-dateadd-function-to-skip-weekend-days.aspx

Note that this functions are not consider the holidays.

Cheers! Uma