Jasinski Technical Wiki

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

PoweredBy

WorkDay Function - SQL Server

RSS
Modified on Mon, Jul 27, 2020, 1:18 PM by Administrator 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-2024, Patrick Jasinski.