Extending DATEADD Function to Skip Weekend Days in TSQL

In this blog we will learn that how we can extend the date and skip a weekend Days in SQL.

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.

Function explanation, and a second implementation

Our function will lay on the predefined DATEADD, first adding the requested number of days, then checking the name of the resultant day. If "sunday", it will add another, day, landing on monday. If "saturday", it adds two days. As it's obvious, an implementation of that kind is useful when we need to skip the weekend days on a single week. If we wish to add a certain number of days, lets say greater than 10, and we wish to skip all the weekend days between our start date and our end one, we could extend the above function to.

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