June 23, 2009 at 9:56 pm
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)
June 23, 2009 at 9:59 pm
this is really great!! good work
June 23, 2009 at 10:01 pm
Thanks for this. smart programming.
August 20, 2009 at 2:07 pm
This is really great!!! Good Work!
August 20, 2009 at 2:20 pm
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
February 13, 2012 at 12:19 am
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,
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply