Calculate upcoming business day from date (Updated)

,

Calculates the next business day based upon the work week
length, number of days from the start date and a list of holiday dates kept in a table called HolidayDate.

Created this script for determining the next banking day from 2 days after a request for wiring funds. Can be used for things like calculating shipping dates and processing dates, etc.

Updated varchar date width

/****** Object:  Table [dbo].[HolidayDate]    Script Date: 7/16/2005 6:47:26 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HolidayDate]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HolidayDate]
GO

/****** Object:  Table [dbo].[HolidayDate]    Script Date: 7/16/2005 6:47:27 AM ******/
CREATE TABLE [dbo].[HolidayDate] (
	[HolidayDate] [smalldatetime] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[HolidayDate] ADD 
	CONSTRAINT [PK_WT_HolidayDate] PRIMARY KEY  CLUSTERED 
	(
		[HolidayDate]
	)  ON [PRIMARY] 
GO


exec sp_addextendedproperty N'MS_Description', N'Date of a holiday', N'user', N'dbo', N'table', N'HolidayDate', N'column', N'HolidayDate'


GO



IF EXISTS (SELECT * from dbo.sysobjects 
	WHERE id = object_id(N'[dbo].[CalcNextBusinessDay]') 
	AND OBJECTPROPERTY(id, N'IsScalarFunction') = 1)
DROP FUNCTION [dbo].[CalcNextBusinessDay]
GO


CREATE FUNCTION dbo.CalcNextBusinessDay ( 
	@dtStartingDate datetime, 
	@tiDaysFromStart tinyint, 
	@tiDaysInWeek tinyint = 5,
	@bStartFromFollowingDay bit = 0)
RETURNS DATETIME
/**************************************************************************
DESCRIPTION: Calculates the next business day based upon the work week
	length, number of days from the start date and a list of
	holiday dates kept in a table called holiday date.

PARAMETERS:
	@dtStartingDate		- Date that calculation will start from
	@tiDaysFromStart 	- Number of business days from start date	
	@tiDaysInWorkWeek	- Number of days in work week. Can be 5,6, or 7.
	@bStartFromFollowingDay - Indicates calculation should begin from day following start
					(used to exclude today's date from being included in
					situations where partially elaspsed days are not counted)
		
RETURNS:
		Datetime of next business day calculated per input criteria

USAGE:
		SELECT dbo.CalcNextBusinessDay('12/23/2005', 10, 5, 1) AS AnticipatedProcessingDate


DEPENDANCIES:
		HolidayDate Table containing the dates of the holidays observed by a 
		particular business.


AUTHOR:	Karen Gayda

DATE: 	07/12/2005

MODIFICATION HISTORY:
	WHO		DATE		DESCRIPTION
	---		----------	---------------------------------------------------
***************************************************************************/
AS
BEGIN
DECLARE @dtNextBusinessDay	datetime,
	@tiBusinessDays		tinyint
	

	
	IF @tiDaysInWeek NOT IN(5,6,7)
	BEGIN
		SET @dtNextBusinessDay =NULL
	END
	ELSE
	BEGIN
		SET @dtNextBusinessDay = @dtStartingDate + @bStartFromFollowingDay
		SET @tiBusinessDays = 0
		WHILE @tiBusinessDays < @tiDaysFromStart
		BEGIN
			SELECT @tiBusinessDays = CASE
				WHEN DATENAME(dw,@dtNextBusinessDay) = 'Saturday' AND @tiDaysInWeek = 5
					THEN @tiBusinessDays 
					 		
				WHEN DATENAME(dw,@dtNextBusinessDay) = 'Sunday' AND @tiDaysInWeek IN(5,6)
					THEN @tiBusinessDays 

				WHEN EXISTS(SELECT HolidayDate FROM HolidayDate 
						WHERE HolidayDate = Cast( Month(@dtNextBusinessDay) as varchar(20)) +
								'/' +  Cast( Day(@dtNextBusinessDay) as varchar(20)) +
								'/' + Cast( Year(@dtNextBusinessDay) as varchar(20)) )
					THEN @tiBusinessDays 
				
				ELSE
					@tiBusinessDays + 1
			END -- case
	
			SELECT @dtNextBusinessDay =  CASE 
				WHEN DATENAME(dw,@dtNextBusinessDay) = 'Saturday' AND @tiDaysInWeek = 5
					THEN DATEADD(dd,2,@dtNextBusinessDay)
					 		
				WHEN DATENAME(dw,@dtNextBusinessDay) = 'Sunday' AND @tiDaysInWeek IN(5,6)
					THEN DATEADD(dd,1,@dtNextBusinessDay)
				ELSE
					DATEADD(dd,1,@dtNextBusinessDay)
			END -- case
		
		END --while
	END --else
	
RETURN (@dtnextBusinessDay)
END

GO

Rate

5 (1)

Share

Share

Rate

5 (1)