add business days to a date

Question

add business days to a date

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004470 More actions · Answer 1

andrewd.smith (12/23/2008)
Here's a method of calculating the Nth working day in the future without using a CTE or tally table.
DECLARE @days int
SELECT @days = 10
DECLARE @inputDate datetime
SELECT @inputDate = '2008-12-23'
DECLARE @weekDay int
SELECT @weekDay = DATEDIFF(day, 0, @inputDate) % 7
/* @weekDay = 0 (Monday), 2 (Tuesday), ... , 5 (Saturday), 6 (Sunday) */
DECLARE @dayOffset int
SELECT @dayOffset = CASE @weekDay
WHEN 6 THEN @days + (@days / 5) * 2 /* Sunday */
WHEN 5 THEN 1 + @days + (@days / 5) * 2 /* Saturday */
ELSE @days + ((@days + @weekDay) / 5) * 2 END
SELECT DATEADD(day, @dayOffset, @inputDate)
The method only works if [font="Courier New"]@days > 0[/font].
If [font="Courier New"]@inputDate[/font] is guaranteed to be a working day (neither Saturday nor Sunday), then the expression simplifies to:
DECLARE @days int
SELECT @days = 10
DECLARE @inputDate datetime
SELECT @inputDate = '2008-12-23'
SELECT DATEADD(day, @days + ((@days + DATEDIFF(day, 0, @inputDate) % 7 ) / 5) * 2, @inputDate)

Whoops... sorry... there's a fly in that ointment...

DECLARE @days int

SELECT @days = 5

DECLARE @inputDate datetime

SELECT @inputDate = '2002-01-05 00:00:00' --Saturday

DECLARE @weekDay int

SELECT @weekDay = DATEDIFF(day, 0, @inputDate) % 7

/* @weekDay = 0 (Monday), 2 (Tuesday), ... , 5 (Saturday), 6 (Sunday) */

DECLARE @dayOffset int

SELECT @dayOffset = CASE @weekDay

WHEN 6 THEN @days + (@days / 5) * 2 /* Sunday */

WHEN 5 THEN 1 + @days + (@days / 5) * 2 /* Saturday */

ELSE @days + ((@days + @weekDay) / 5) * 2 END

SELECT DATEADD(day, @dayOffset, @inputDate) [font="Arial Black"]--Returns 2002-01-13 00:00:00.000... a Sunday[/font]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004470 More actions · Answer 2

Fellow who goes by the handle "Fraggle" posted a function at the following URL... it seems to work quite reliably for the days ahead that I tested it for...

http://www.sqlservercentral.com/Forums/Topic153606-203-3.aspx#BM591629

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 3

Apologies for my stupid mistake in the expression for Saturdays and Sundays.

The following is a corrected version. Note that [font="Courier New"]@days/5[/font] is replaced by [font="Courier New"](@days-1)/5[/font] in the expression for Saturdays and Sundays.

DECLARE @days int

SELECT @days = 5

DECLARE @inputDate datetime

SELECT @inputDate = '2002-01-05'

DECLARE @weekDay int

SELECT @weekDay = DATEDIFF(day, 0, @inputDate) % 7

/* @weekDay = 0 (Monday), 2 (Tuesday), ... , 5 (Saturday), 6 (Sunday) */

DECLARE @dayOffset int

SELECT @dayOffset = CASE @weekDay

WHEN 6 THEN @days + ((@days - 1) / 5) * 2 /* Sunday */

WHEN 5 THEN 1 + @days + ((@days - 1) / 5) * 2 /* Saturday */

ELSE @days + ((@days + @weekDay) / 5) * 2 END

SELECT DATEADD(day, @dayOffset, @inputDate)

The above returns the date 2002-01-11 (Friday) rather than 2002-01-13 (Sunday).

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 4

Here's my take. )

declare @numberBusinessDaysToAdd int

declare @basedate datetime

set @numberBusinessDaysToAdd = 14

set @basedate = '20090101 17:30'

-- first move base date on to the start of the next day

set @basedate = dateadd(d, datediff(d, '19700101', @basedate) + 1, '19700101')

-- move base date on to the first working day (if not already)

DECLARE @0BasedDayOfWeek int

SET @0BasedDayOfWeek = datediff(d, '19700105', @basedate) % 7 -- get differential from a known Monday

IF @0BasedDayOfWeek > 4 -- if we're saturday or Sunday

SET @basedate = dateadd(d, 7 - @0BasedDayOfWeek, @basedate) -- then add the appropriate number of days to make it to the next monday

-- now add weeks / days as appropriate

set @basedate = dateadd(d, @numberBusinessDaysToAdd % 5, dateadd(ww, @numberBusinessDaysToAdd / 5, @basedate))

Edit -> changed a couple of variable names

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone