4 UDFs to manipulate Working/Business days

All following functions requare a Holidays table.
It should contain a field called Holiday of datetime datatype.

It might contain other fields like :

HolidayName Varchar(50) or whatever you need.


CREATE TABLE [Holidays] (
[Holiday] [datetime] NOT NULL)



Insert your Nonworking days (except weekends)in the table
For example:


INSERT INTO [Holidays]
VALUES('2005-12-24')






/********************************************************************************************
****
**** returns next working day
****
**********************************************************************************************/
CREATE FUNCTION fNextWorkingDay (@Start datetime)
RETURNS datetime
AS
BEGIN
DECLARE @NextDay datetime

-- First, add a single day

SET @NextDay = DATEADD(d, 1, @Start)

-- Now, depending on the day of the week, move it forward
-- to the next weekday. Note this assume -- s that the first
-- day of the week is Sunday, the defaul -- t.

SET @NextDay = CASE DATEPART(dw, @NextDay)
WHEN 1 THEN DATEADD(d, 1, @NextDay)
WHEN 7 THEN DATEADD(d, 2, @NextDay)
ELSE @NextDay
END

-- Now look up the date in the holiday table, looping
-- to the next clear weekday.
WHILE EXISTS(SELECT Holiday FROM Holidays WHERE Holiday = @NextDay)
BEGIN
SET @NextDay = DATEADD(d, 1, @NextDay)
SET @NextDay = CASE DATEPART(dw, @NextDay)
WHEN 1 THEN DATEADD(d, 1, @NextDay)
WHEN 7 THEN DATEADD(d, 2, @NextDay)
ELSE @NextDay
END
END

-- Finally, return the next working date
RETURN @NextDay
END



/********************************************************************************************
****
**** returns previous working day
****
**********************************************************************************************/
CREATE FUNCTION fPreviousWorkingDay (@Start datetime)
RETURNS datetime
AS
BEGIN
DECLARE @PrevDay datetime

-- First, add a single day

SET @PrevDay = DATEADD(d, -1, @Start)

-- Now, depending on the day of the week, move it backward
-- to the next weekday. Note this assume -- s that the first
-- day of the week is Sunday, the defaul -- t.

SET @PrevDay = CASE DATEPART(dw, @PrevDay)
WHEN 1 THEN DATEADD(d, -2, @PrevDay)
WHEN 7 THEN DATEADD(d, -1, @PrevDay)
ELSE @PrevDay
END

-- Now look up the date in the holiday table, looping
-- to the next clear weekday.

WHILE EXISTS(SELECT Holiday FROM Holidays WHERE Holiday = @PrevDay)
BEGIN
SET @PrevDay = DATEADD(d, -1, @PrevDay)
SET @PrevDay = CASE DATEPART(dw, @PrevDay)
WHEN 1 THEN DATEADD(d, -2, @PrevDay)
WHEN 7 THEN DATEADD(d, -1, @PrevDay)
ELSE @PrevDay
END
END



-- Finally, return the next working date

RETURN @PrevDay
END


/********************************************************************************************
**** returns date after given number of working days
**** uses function fNextWorkingDay
****
**********************************************************************************************/
CREATE FUNCTION fAddWorkingDay (@Start datetime, @NumOfDays integer)
RETURNS datetime
AS
BEGIN
DECLARE @NextDay datetime
DECLARE @Counter int

IF @NumOfDays>0
BEGIN
SET @Counter=1
-- Drop the time part and initialize the Return Variable

SET @NextDay=CONVERT(datetime ,(CONVERT(char(10),@Start,102)),102 )

WHILE @Counter <= @NumOfDays
BEGIN
SET @NextDay = dbo.fNextWorkingDay(@NextDay)
SET @Counter=@Counter+1
END
END
ELSE
BEGIN
SET @Counter=ABS(@NumOfDays)
-- Drop the time part and initialize the Return Variable

SET @NextDay=CONVERT(datetime ,(CONVERT(char(10),@Start,102)),102 )

WHILE @Counter>0
BEGIN
SET @NextDay = dbo.fPreviousWorkingDay(@NextDay)
SET @Counter=@Counter-1
END
END

RETURN @NextDay
END


/********************************************************************************************
**** returns number of working days between given dates
**** (negative values indicates then the second date is earlier than the first one)
****
**********************************************************************************************/

CREATE FUNCTION fWorkingDaysBetween (@StartDay datetime, @EndDay datetime)
RETURNS int
AS
BEGIN

DECLARE
@date1 datetime,
@date2 datetime,
@tempdate datetime,
@day int,
@count int,
@multiplier int

IF @StartDay < @EndDay
BEGIN
SET @date1 = CONVERT(datetime ,(CONVERT(char(10),@StartDay,102)),102 )
SET @date2 = CONVERT(datetime ,(CONVERT(char(10),@EndDay,102)),102 )
SET @multiplier=1
END
ELSE
BEGIN
SET @date2 = CONVERT(datetime ,(CONVERT(char(10),@StartDay,102)),102 )
SET @date1 = CONVERT(datetime ,(CONVERT(char(10),@EndDay,102)),102 )
SET @multiplier=-1
END

SET @tempdate = @date1
SET @count = 0

/* Process till tempdate becomes date2 */
WHILE ( datediff(dd,@tempdate,@date2) >= 0)
BEGIN
SET @day = Datepart(dw,@tempdate)
/* If it is not a Saturday or a Sunday */
IF (@day != 1 AND @day != 7)
/* If it is not a Public Holiday */
IF NOT EXISTS(SELECT * FROM holidays WHERE holiday= @tempdate)
/* Increment @tempdate by 1 day */
SELECT @count = @count + 1

ET @tempdate = Dateadd(dd,1,@tempdate)
END

RETURN @count * @multiplier
END





Wise men talk because they have something to say; fools, because they have to say something.
Plato

Hello Mike and thank you for this post, now is a bit back in years but see if you are still around

I am fearly new to SQL and when I try to execute the function for previous working day it keeps asking
a value for @Start. Could you please clarify this? I have not seen any value allocated to this variable
at all. Again I am very new to SQL and perhaps I am missing / not aware of something?
Arthur