Navigation

	Home Page
	Index
	All Pages

Quick Search
Advanced Search »

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

WorkDay Function - SQL Server

This function reproduces the Excel WORKDAY() function, which adds a number of business days to a given date.

For a C# version of this function, see AddBusinessDays Function - C#.

/*--------------------------------------------------------------------------------
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