| Home |

SQL Server Function to add working days on to a date

This is my first post so please be nice to me… I have recently been asked to look at automating the recalculation of the SLA target on helpdesk calls which had the clock stopped prior to closure. When I had my first look it seemed nice and simple… you have a number of days to add to the target date so all I would need is a DATEADD() function and kablamo something that was taking a person a day a month today could be done in a matter of seconds.

However when I went into a bit more detail however it appears that what they wanted was to add on only working days, e.g. if the target goal is 2 days and the target date is on a Friday this should actually recalculate the target date as Tuesday. So this raised the question what is the simplest way to add 2 working days to a date.

Now I am not saying that my solution is the most elegant solution (generally I try and avoid using functions if I can) in the world but it certainly seemed to work for me… what I decided to do was create a function which would do the work for me, the reason for doing it this way was partially for speed and partially as it could be a fairly useful tool for other things.

Below is the SQL used to create the function :

CREATE FUNCTION AddWorkDays 
(    
    @WorkingDays As Int, 
    @StartDate AS DateTime 
) 
RETURNS DateTime 
AS 
BEGIN 
    DECLARE @Count AS Int 
    DECLARE @i As Int 
    DECLARE @NewDate As DateTime 
    SET @Count = 0 
    SET @i = 0 

    WHILE (@i < @WorkingDays) --runs through the number of days to add 
    BEGIN 
-- increments the count variable 
        SELECT @Count = @Count + 1 
-- increments the i variable 
        SELECT @i = @i + 1 
-- adds the count on to the StartDate and checks if this new date is a Saturday or Sunday 
-- if it is a Saturday or Sunday it enters the nested while loop and increments the count variable 
           WHILE DATEPART(weekday,DATEADD(d, @Count, @StartDate)) IN (1,7) 
            BEGIN 
                SELECT @Count = @Count + 1 
            END 
    END 

-- adds the eventual count on to the Start Date and returns the new date 
    SELECT @NewDate = DATEADD(d,@Count,@StartDate) 
    RETURN @NewDate 
END 
GO

 

Hope it is helpful, it got me by at least :)

 

posted @ Friday, August 21, 2009 10:57 AM
Print

Comments on this entry:

# re: SQL Server Function to add working days on to a date

Left by cal at 1/29/2010 7:32 PM
Gravatar
tidy looking good

# re: SQL Server Function to add working days on to a date

Left by barry scot at 1/29/2010 7:33 PM
Gravatar
thanks for the post

Folding Camp Table

# re: SQL Server Function to add working days on to a date

Left by Soap Distant at 4/20/2010 11:01 AM
Gravatar
Thanks for the work, saves me a lot of time!

# re: SQL Server Function to add working days on to a date

Left by aniket at 6/24/2010 3:05 PM
Gravatar
thanks. it helped a lot.

# re: SQL Server Function to add working days on to a date

Left by sree at 8/23/2010 12:42 PM
Gravatar
Good job:)Keep up the great work...

# re: SQL Server Function to add working days on to a date

Left by sree at 8/24/2010 11:53 AM
Gravatar
it doesnt work if @WorkingDays is negative??

# re: SQL Server Function to add working days on to a date

Left by Ivan Zitko at 5/11/2011 11:12 AM
Gravatar
Hi,

I would add little more code to be sure that result will be ok. Your function works fine when @@DATEFIRST variable is set to default (7), but if it is changed, function returns wrong result. No problem if you write function with little changes like this:

ALTER FUNCTION [dbo].[AddWorkDays] (@WorkingDays As Int,@StartDate AS DateTime) RETURNS DateTime
AS BEGIN

DECLARE @Count AS Int
DECLARE @i As Int
DECLARE @NewDate As DateTime
DECLARE @CurrentFirstDayInWeek int
DECLARE @DateFirstDifference int
DECLARE @SatNo int
DECLARE @SunNo int

SELECT @CurrentFirstDayInWeek = @@DATEFIRST
SET @DateFirstDifference = 7 - @CurrentFirstDayInWeek
SET @SatNo = 7
SET @SunNo = 1

IF @DateFirstDifference > 0
BEGIN
SET @SatNo = @DateFirstDifference
SET @SunNo = 1 + @DateFirstDifference
END

SET @Count = 0
SET @i = 0
WHILE (@i < @WorkingDays) --runs through the number of days to add
BEGIN
-- increments the count variable
SELECT @Count = @Count + 1

-- increments the i variable
SELECT @i = @i + 1

-- adds the count on to the StartDate and checks if this new date is a Saturday or Sunday
-- if it is a Saturday or Sunday it enters the nested while loop and increments the count variable
WHILE DATEPART(weekday,DATEADD(d, @Count, @StartDate)) IN (@SatNo, @SunNo)
BEGIN
SELECT @Count = @Count + 1
END
END

-- adds the eventual count on to the Start Date and returns the new date
SELECT @NewDate = DATEADD(d,@Count,@StartDate)

RETURN @NewDate

END

I hope this will be helpfull for someone... :)

# re: SQL Server Function to add working days on to a date

Left by Sanyog Tomar at 6/17/2011 10:15 AM
Gravatar
Thankx Dear,it's working fine.

# re: SQL Server Function to add working days on to a date

Left by MGB at 7/18/2011 10:54 AM
Gravatar
Great job! thanks!

# re: SQL Server Function to add working days on to a date

Left by Khurram Shahzad at 7/25/2011 2:16 PM
Gravatar
Thanks for great help.

# re: SQL Server Function to add working days on to a date

Left by Last Minute at 9/28/2011 1:00 PM
Gravatar
Whilse loop is going to work poorly over a record set. This will scale better
DECLARE
@DirectionSign INT
,@WorkingDayCount SMALLINT
,@ReferenceDate DATE
,@SundayAllowance SMALLINT
,@SaturdayAllowance SMALLINT ;

SELECT
@DirectionSign = -1 -- +1 forward, -1 historical date
,@SundayAllowance = CASE @DirectionSign
WHEN -1 THEN -2
ELSE 1
END
,@SaturdayAllowance = CASE @DirectionSign
WHEN -1 THEN -1
ELSE 2
END
,@WorkingDayCount = 53 -- figure that you assign
,@ReferenceDate = GETDATE() -- figure that you assign
,@WorkingDayCount = @DirectionSign * ABS(@WorkingDayCount) ;

SELECT
@ReferenceDate AS ReferenceDate
,@WorkingDayCount AS WorkingDaysOffset
,DATEADD(dd
,CASE DATENAME(dw,
DATEADD(dd,@WorkingDayCount%5
,(DATEADD(wk,(@WorkingDayCount/5),@ReferenceDate))
)
)
WHEN 'Sunday' THEN @SundayAllowance
WHEN 'Saturday' THEN @SaturdayAllowance
ELSE 0
END
,DATEADD(dd,@WorkingDayCount%5,(DATEADD(wk,(@WorkingDayCount/5),@ReferenceDate)))
) AS OffsetWorkingDate

Formatting lost sorry

# re: SQL Server Function to add working days on to a date

Left by siva at 10/7/2011 2:01 AM
Gravatar
very kool function. Thanks. It saved me a lot of time.

# re: SQL Server Function to add working days on to a date

Left by D at 1/5/2012 2:05 PM
Gravatar
Issues identified with the original function:

- Fails with negative work days
- Fails if @@DATEFIRST is set to anything other than 7
- Loops have poor performance over a record set

Here's an attempt to address the identified issues:



CREATE FUNCTION dbo.dateaddworkdays
(
@workdays int,
@date datetime
)
RETURNS datetime
AS
BEGIN
DECLARE
@sat int,
@sun int,
@weeks int,
@remain_days smallint,
@day_o_week smallint,
@current_day smallint

-- no change
IF @workdays = 0
RETURN @date

-- number of whole weeks
SET @weeks = @workdays / 5
-- days of partial week
SET @remain_days = @workdays % 5
-- starting day of the week
SET @current_day = datepart(WEEKDAY, @date)
-- day of the week for date plus the partial days; this may go beyond the
-- bounds of 1 and 7, which is desirable if it happens.
SET @day_o_week = @current_day + @remain_days

-- are days being added?
IF @workdays > 0
BEGIN
-- value of Saturday
SET @sat = 7 - @@DATEFIRST

-- working with days after the current day. Make sure the value of
-- Saturday reflects this, even if it goes beyond 7.
IF @sat < @current_day
SET @sat = @sat + 7

-- set Sunday accordingly
SET @sun = @sat + 1

-- the calculation for weekends will be that one business day after
-- Sunday is equal to Tuesday instead of Monday. Possibly add a
-- parameter for the caller to make that decision.
IF @current_day = @sun
OR @current_day + 7 = @sun
RETURN @date + @weeks * 7 + @remain_days + 1

-- If starting on a Saturday or crossing the weekend boundary, subtract
-- two more days.
IF @day_o_week >= @sat
OR @current_day = @sat
RETURN @date + @weeks * 7 + @remain_days + 2

-- neither started on a weekend, nor crossed the weekend.
RETURN @date + @weeks * 7 + @remain_days
END
-- ELSE days are being subtracted

-- working with days before the current day. Make sure the value of
-- Sunday reflects this, even if it goes below 1...
SET @sun = 1 - @@DATEFIRST

-- ... but no so far below that it's more than 6 than the current day.
IF @current_day > @sun + 7
SET @sun = @sun + 7

-- set Saturday accordingly
SET @sat = @sun - 1

-- the calculation for weekends will be that one business day before
-- Saturday is equal to Thursday instead of Friday. Possibly add a
-- parameter for the caller to make that decision.
IF @current_day = @sat
OR @current_day - 7 = @sat
RETURN @date + @weeks * 7 + @remain_days - 1

-- If starting on a Sunday or crossing the weekend boundary, subtract two
-- more days.
IF @day_o_week <= @sun
OR @current_day = @sun
RETURN @date + @weeks * 7 + @remain_days - 2

-- neither started on a weekend, nor crossed the weekend.
RETURN @date + @weeks * 7 + @remain_days
END
GO

# re: SQL Server Function to add working days on to a date

Left by D at 1/5/2012 2:50 PM
Gravatar
One minor error in the post above among the lines that read

-- ... but no so far below that it's more than 6 than the current day.
IF @current_day > @sun + 7

The comment is correct. The number in the IF statement should be 6, not 7.

# re: SQL Server Function to add working days on to a date

Left by jellian at 1/18/2012 2:22 AM
Gravatar
how to add days not including sunday?

# re: SQL Server Function to add working days on to a date

Left by Madhur Bhorali at 2/14/2012 1:07 PM
Gravatar
Great Man. It saved my time a lot. Thanks

# re: SQL Server Function to add working days on to a date

Left by DAD at 6/26/2012 9:24 PM
Gravatar
Thank you so much , Your solution for was very helpful to me.
please keep posting.

Thanks Again

# re: SQL Server Function to add working days on to a date

Left by Gus Cast at 7/2/2012 6:46 PM
Gravatar
Thank you. Nice and simple. Very good!

# re: SQL Server Function to add working days on to a date

Left by Craig at 8/24/2012 8:44 AM
Gravatar
Thank you, seen people moaning about the weekend situation, I got around this by doing this:

WHILE DATENAME(WEEKDAY,DATEADD(d,@Count,@StartDate)) IN ('Saturday','Sunday')
BEGIN
...

Works like a charm

# re: SQL Server Function to add working days on to a date

Left by yarr at 1/30/2013 12:56 PM
Gravatar
@D at 1/5/2012 2:05 PM

Hello D,

in fact, your function is also having an other Typo mistake:
it is NOT:
-- value of Saturday
SET @sat = 7 - @@DATEFIRST

-- working with days after the current day. Make sure the value of
-- Saturday reflects this, even if it goes beyond 7.
IF @sat < @current_day
SET @sat = @sat + 7

BUT IT SHOULD BE:

-- value of Saturday
SET @sat = 6 - @@DATEFIRST

-- working with days after the current day. Make sure the value of
-- Saturday reflects this, even if it goes beyond 7.
IF @sat < @current_day
SET @sat = @sat + 6

-> saturday = 6 th day, not the 7th. It was basically making some of the results ending on Sunday and thus, making 10 WD working alike 8 WD... :)

But great code otherwhise :)

Yarr

# df,df,df

Left by sd at 2/20/2013 5:46 AM
Gravatar
wwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevwewewdv vvvvvvvvvvvvvasdfwerasdvevewewdv vvvvvvvvvvvvvasdfwerasdvev

# Negative Days

Left by MM at 10/17/2013 11:35 AM
Gravatar
This worked brilliantly for me. My requirement does include subtracting days, which I got round by
> passing in a negative parameter
> adding +1 or -1 in the loop according to whether the parameter is +ve or -ve respectively
> comparing to ABS (input) in the while loop
I've also renamed the @Count variable to @CalendarDays for clarity

Thanks so much, this has saved my project a lot of time!

# re: SQL Server Function to add working days on to a date

Left by BLIP BLIP at 10/25/2013 11:18 AM
Gravatar
BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP ...

BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP

BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP
BLIP BLIP BLIP BLIP BLIP BLIP
BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP BLIP

# re: SQL Server Function to add working days on to a date

Left by sushant at 11/22/2013 9:18 AM
Gravatar
how to use that.??

Your comment:



(not displayed)

 
 
 
 
 

Live Comment Preview:

 
«July»
SunMonTueWedThuFriSat
293012345
6789101112
13141516171819
20212223242526
272829303112
3456789