Fraggle (10/24/2008)
Jeff,Alright, now that I have had a little time to ponder the imponderable as my dad would say, I figured out how to add X number of business days to a start date.
Create Function fn_AddBusinessDays
(@StartDate DATETIME,
@DaysToAdd TINYINT
)
Returns DATETIME
AS
BEGIN
DECLARE @EndDate DATETIME
SET @EndDate = DateAdd(Week, @DaysToAdd/5, @StartDate)
+ CASE
WHEN DatePart(dw, @StartDate) + @DaysToAdd % 5 >= 7
THEN @DaysToAdd % 5 + 2
ELSE @DaysToAdd % 5 --else justadd the day
END
RETURN CASE
WHEN DATENAME(dw, @EndDate) = 'Saturday'
THEN DateAdd(dd, 2, @EndDate)
WHEN DATENAME(dw, @EndDate) = 'Sunday'
THEN DATEADD(dd,1,@EndDate)
ELSE @EndDate
End
END
See what a little time will do. No RBAR!
Fraggle
Hey there, Fraggle... I know it's been a while, but I thought that I'd tell you that not only did I test your good function, but I've used it quite a few times. The cool thing about it is that you can easily use it on a whole bunch of dates. When you trying to do the same thing with a calendar table, you have to do a subquery with something like ...
SELECT MIN(calen_dt) FROM dbo.Calender WHERE calen_dt >= DATEADD(d, @intErval, @dteCurrentDate) AND flg_bdate='Y'
Now, I'll admit that your function doesn't handle holiday's like a calendar table might... but if you had a nice holiday table...
Anyway, thanks again for the function.
--Jeff Moden
Change is inevitable... Change for the better is not.