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

DateAdd function for Weekdays Expand / Collapse
Author
Message
Posted Wednesday, August 3, 2005 4:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 23, 2006 9:31 AM
Points: 1, Visits: 1

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

 

Post #207135
Posted Monday, August 8, 2005 8:00 AM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,039, Visits: 1
No one has responded to this topic yet. Even if you don't have a complete answer, the original poster will appreciate any thoughts you have!
Post #208338
Posted Wednesday, November 17, 2010 5:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 7:55 AM
Points: 14, Visits: 82
thank you very helpful!!!
Post #1022084
Posted Wednesday, November 17, 2010 5:32 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:57 AM
Points: 1,949, Visits: 8,304
Since this has been bumped ,

a calendar table might be a good option here

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html




Clear Sky SQL
My Blog
Kent user group
Post #1022086
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse