Extending DATEADD Function to Skip Weekend Days

Introduction

Sometimes, speaking about date extraction or calculation from a database, it could be required to take into account weekend days. For example, we are managing order dates, and we need to postpone them. If shipments can't be done on weekends, it could be useful to have a DATEADD-like function to postpone our date to a working day. The following code will accomplish the task.

Using the Code

Just copy and paste the following into SQL Management Studio: a new function will be created to be used in future queries.

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

The new function can be executed like usual ones:

SELECT dbo.DAYSADDNOWK(GETDATE(), 3)

where GETDATE() function can be substituted by the needed date, and the example value of "3", representing the number of days to be added, could be replaced by any value in the scope of INT.

History

• 2015-04-17: First release for CodeProject