Share via

T-SQL: Extending DATEADD Function to Skip Weekend Days

  • Article

Introduction

Since businesses are often only open week days they often require calculations to be in terms of "working days" and excluding weekends. For example, it might take so many working days to deliver an order. We would need to calculate just on the basis of Monday through Friday and only promise to deliver on a weekday.

In order to handle this sort of requirement, it would be useful to have a DATEADD-like function which excludes Saturday and Sunday and counts only working days. The following code will accomplish the task.

Using the Code

Copy and paste the following into SQL Management Studio and hit Run to create a new function available for 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:

SELECT dbo.DAYSADDNOWK(GETDATE(), 3)

Here GETDATE() will likely be substituted for your own calculation or function returning the current date or date to count from. The value "3", is the number of days to add and could be replaced by any integer value.

Function explanation, and a second implementation

The above function is rather simplistic in that it first adds the requested number of days, then checks what that gives. It then adds 2 or 1 days for Saturday or Sunday in order to give the next Monday. Whilst useful if we take orders and deliver within 7 days there are obviously some shortcomings to this.

Say you need 10 working days to get the product in from a third party and make it into whatever we sell. This is not going to allow for the Saturday and Sunday and we could end up promising a date which we can't fulfill.

A function which just counted week days and ignored Saturday and Sunday completely in the calculation would solve this issue.

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

Other languages

The article is available in the following localizations: