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
Note that this functions are not consider the holidays.
Cheers!
Uma
