Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Finding next or previous nth business day excluding weekends Expand / Collapse
Author
Message
Posted Tuesday, June 23, 2009 9:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, June 23, 2009 9:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, April 6, 2014 8:46 PM
Points: 1, Visits: 75
this is really great!! good work
Post #740731
Posted Tuesday, June 23, 2009 10:01 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 7, 2013 6:12 PM
Points: 135, Visits: 87
Thanks for this. smart programming.
Post #740733
Posted Thursday, August 20, 2009 2:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 13, 2014 7:03 AM
Points: 2, Visits: 93
This is really great!!! Good Work!
Post #774569
Posted Thursday, August 20, 2009 2:20 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Posted Monday, February 13, 2012 12:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 19, 2013 2:25 AM
Points: 8, Visits: 69
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 »

Add to briefcase

Permissions Expand / Collapse