Thursday, November 17, 2016

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

Cheers! Uma

2 comments: