MSSQLCity.Com - All about MS SQL
     
About Us  
SSWUG Articles  
Articles  
FAQ  
Administration  
Backup/Restore  
Connectivity  
Development  
General  
Installation  
OLAP  
Replication  
Transfer/move  
Trouble  
SQL 6.5  
Scripts  
Tips  
Test Exams  
Advertise  
Download  
History  
Search  
Traffic  
Related Links  
     
Your button logo
Add to Favorites
 
     
 


How do I add 'n' working days to the given date and get the result date?

Answer:

To add 'n' working days to the given date and return the result date, you can use AddWorkingDays user-defined function. The AddWorkingDays udf works like DATEADD, but adds the working days.

Syntax

AddWorkingDays ( StartDate, WorkDays )

Arguments

StartDate
Is the datetime value (start date).

WorkDays
Is the integer value (number of working days).

Return Types

datetime

The function's text:

CREATE FUNCTION dbo.ADDWorkingDays
  ( @StartDate datetime,
    @WorkDays int )
RETURNS datetime
AS
BEGIN
  DECLARE @TotalDays int, @FirstPart int
  DECLARE @EndDate datetime
  DECLARE @LastNum int, @LastPart int

  IF @WorkDays < 0
     BEGIN
       SELECT @FirstPart = CASE DATENAME(weekday, @StartDate)
                 WHEN 'Sunday' THEN 0
                 WHEN 'Monday' THEN 1
                 WHEN 'Tuesday' THEN 2
                 WHEN 'Wednesday' THEN 3
                 WHEN 'Thursday' THEN 4
                 WHEN 'Friday' THEN 5
                 WHEN 'Saturday' THEN 6
               END
       IF ABS(@WorkDays) < @FirstPart
          SELECT @EndDate = DATEADD(dd, @WorkDays, @StartDate)
       ELSE
         BEGIN
           SELECT @TotalDays = (ABS(@WorkDays) - @FirstPart) / 5
           SELECT @LastPart = (ABS(@WorkDays) - @FirstPart) % 7
           SELECT @LastNum = CASE
             WHEN (@LastPart < 7) AND (@LastPart > 0) THEN @LastPart - 1
             ELSE 0
           END
           SELECT @TotalDays = - 2 * (@TotalDays + 1) + @WorkDays
           SELECT @EndDate = DATEADD(dd, @TotalDays, @StartDate)
         END
     END

  ELSE

     BEGIN
       SELECT @FirstPart = CASE DATENAME(weekday, @StartDate)
                 WHEN 'Sunday' THEN 6
                 WHEN 'Monday' THEN 5
                 WHEN 'Tuesday' THEN 4
                 WHEN 'Wednesday' THEN 3
                 WHEN 'Thursday' THEN 2
                 WHEN 'Friday' THEN 1
                 WHEN 'Saturday' THEN 0
               END
       IF @WorkDays < @FirstPart
          SELECT @EndDate = DATEADD(dd, @WorkDays, @StartDate)
       ELSE
         BEGIN
           SELECT @TotalDays = (@WorkDays - @FirstPart) / 5
           SELECT @LastPart = (@WorkDays - @FirstPart) % 7
           SELECT @LastNum = CASE
             WHEN (@LastPart < 7) AND (@LastPart > 0) THEN @LastPart - 1
             ELSE 0
           END
           SELECT @TotalDays = 2 * (@TotalDays + 1) + @WorkDays
           SELECT @EndDate = DATEADD(dd, @TotalDays, @StartDate)
         END
     END

  RETURN ( @EndDate )

END
GO

Example

Adds 9 working days to '11/13/2002' and returns the result date:

SELECT dbo.AddWorkingDays ('11/13/2002', 9)
GO
Here is the result set:

------------------------------------------------------ 
2002-11-26 00:00:00.000

(1 row(s) affected)
Note. The user-defined functions are only available with SQL Server 2000, SQL Server 7.0 does not support user-defined functions.

See this article for more datetime user-defined functions:
Date and Time User-Defined Functions


 

 
Visit The SQL Server Worldwide User's Group for all the latest news and information about SQL Server, Oracle, DB2 and XML for developers and administrators.

(c) 1997, 2010 Bits on the Wire, Inc