August 3, 2005 at 4:46 pm
Here's a function for doing dateadd for weekdays only. (e.g., what weekday is 43 weekdays from today?) Hope it is helpful.
CREATE FUNCTION dbo.weekday_dateadd ( @nbrOfDays int, @startDate smalldatetime )
RETURNS smalldatetime
AS
BEGIN
/*
Title: weekday_dateadd
Author: Jesse Roberts
Date: 08/03/2005
Purpose: Adds a specified number of weekdays to a given date and returns the resulting date
*/
declare @adj_start smalldatetime, @endDate smalldatetime
declare @adj_nbrOfDays real, @adj_dayofweek int
declare @numweeks int, @adj_enddays int, @calendar_days int
-- get to the last Monday prior to the start date
set @adj_dayofweek = (datepart(dw,@startDate) - 2)
set @adj_start = dateadd(d, -(@adj_dayofweek), @startDate)
-- round to the nearest multiple of 5
set @adj_nbrOfDays = round(@nbrOfDays / 5, 0) * 5
-- adjust the rounding product to ensure the result is the last multiple of 5 less than @increment
if @adj_nbrOfDays > @nbrOfDays
begin
set @adj_nbrOfDays = @adj_nbrOfDays - 5
end
-- add up the number of calendar days to add back to the end date
set @adj_enddays = (@nbrOfDays - @adj_nbrOfDays) + @adj_dayofweek
if @adj_enddays > 4 -- if @adj_enddays is > 4, then it means Friday of the week was passed since 2 (Mon) + 4 = 6 (Fri)
begin
set @adj_enddays = @adj_enddays + 2 -- add 2 days to compensate for the Sat/Sun wrap
end
set @calendar_days = (@adj_nbrOfDays * 1.4) + @adj_enddays
set @endDate = dateadd(d, @calendar_days, @adj_start)
set @endDate = case datepart(dw,@endDate)
when 7 then dateadd(d,2,@endDate)
when 1 then dateadd(d,2,@endDate)
else @endDate
end
RETURN @endDate
END
August 8, 2005 at 8:00 am
This was removed by the editor as SPAM
November 17, 2010 at 5:26 am
thank you very helpful!!!
November 17, 2010 at 5:32 am
Since this has been bumped ,
a calendar table might be a good option here
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply