Finding next or previous nth business day excluding weekends

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Rob Scholl » Finding next or previous nth business day...

Finding next or previous nth business day excluding weekends

Rate Topic

Display Mode

Topic Options

Author

Message

maluthwatta

Posted Tuesday, June 23, 2009 9:56 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, April 22, 2010 12:25 AM
Points: 1, Visits: 51

declare @date datetime
declare @days int
declare @weeks int

select @date = '2009-06-21'
select @days = 1

if (@days < 0)
begin
if datepart (dw, @date) = 1
select @date = dateadd (d, -1, @date)
select @weeks = (datepart (dw, @date) + @days - 6)/5
end
else
begin
if datepart (dw, @date) = 7
select @date = dateadd (d, 1, @date)
select @weeks = (datepart (dw, @date) + @days - 2)/5
end

select dateadd (d, @days + (@weeks*2), @date)

Post #740730

Udula Karunathilake

Posted Tuesday, June 23, 2009 9:59 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 6:11 PM
Points: 1, Visits: 52

this is really great!! good work

Post #740731

Bimal Fernando

Posted Tuesday, June 23, 2009 10:01 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 17, 2012 3:53 AM
Points: 135, Visits: 77

Thanks for this. smart programming.

Post #740733

Sherrill Herron

Posted Thursday, August 20, 2009 2:07 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 03, 2013 12:36 PM
Points: 2, Visits: 80

This is really great!!! Good Work!

Post #774569

GSquared

Posted Thursday, August 20, 2009 2:20 PM

SSCoach

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442, Visits: 9,571

What about holidays?

Your best bet for this kind of thing is a calendar table. Lots of uses, this is one of them.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #774573

Jyots

Posted Monday, February 13, 2012 12:19 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:24 PM
Points: 1, Visits: 22

Hi,

Thanks for the code. It really useful.

Can you please explain the logic, so it will be easy to understand how it works.

When @days are less than 0 you have done

SELECT @weeks = (datepart (dw, @date) + @days - 6)/5

and when @days are greater than 0

then

SELECT @weeks = (DATEPART (dw, @date) + @days - 2)/5

how it works.

Thanks & Regards,

Post #1251009

« Prev Topic | Next Topic »

Permissions