### Navigation

Quick Search
»
Advanced Search »

### Contributor Links

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

# WorkDay Function - SQL Server

Modified on Mon, Jul 27, 2020, 1:18 PM Categorized as Microsoft Office, 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```

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2020, Patrick Jasinski.