/*-------------------------------------------------------------------------------- Credits: Patrick Jasinski, 03/24/08 Purpose: This function reproduces the Excel function WORKDAY() Algorithm: (1) Add 7 days per whole week (i.e,. whole groups of 5 days) (2) For the "extra days", if adding them directly to the above result involves a weekend, Sample Use: select dbo.WorkDay ('4/16/2008', -6) as result --------------------------------------------------------------------------------*/ create function [dbo].[WorkDay] (@inputDate datetime, @offset int) returns datetime as begin declare @result datetime ,@wholeWeeks int ,@extraDays int ,@result2 datetime ,@result3 datetime -- First Attempt select @wholeWeeks = floor(abs(@offset) / 5) * sign(@offset) ,@result = dateadd(d, @wholeWeeks * 7, @inputDate) ,@extraDays = @offset % 5 ,@result2 = dateadd(d, @extraDays, @result) --print 'Whole Weeks = ' + convert(varchar(3), @wholeWeeks) --print 'Extra Days = ' + convert(varchar(3), @extraDays) if (datepart(dw, @result2) < datepart(dw,@result) and @offset > 0) or (datepart(dw, @result2) > datepart(dw,@result) and @offset < 0) or (datepart(dw, @result2) % 6 = 1) select @wholeWeeks = @wholeWeeks + sign(@offset) ,@extraDays = @extraDays - 5 * sign(@offset) --print 'Whole Weeks = ' + convert(varchar(3), @wholeWeeks) --print 'Extra Days = ' + convert(varchar(3), @extraDays) set @result3 = dateadd(d, @wholeWeeks * 7 + @extraDays, @inputDate) --select -- @result as result -- ,@result2 as result2 -- ,@result3 as result3 return @result3 END