Extending DATEADD Function to Skip Weekend Days

Emiliano Musso

4.57/5 (4 votes)

17 Apr 2015CPOL

33.9K

A quick method to add days to a date, skipping weekend days if the number of days added results in a Saturday/Sunday

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.

SQL

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:

SQL

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)