WorkingDaysDiff complementary UDF for DATEDIFF

,

We frequently have to work out a date based on a number of working days before or after a specified date, excluding Saturdays and Sundays.

Attached is the script to create the function.  I call another function called fn_Midnight which was designed to help whe programmers get a date with no time portion without having to remember the CONVERT function (actually, at the time I didn't know it either, but it works).

Hope you find it useful.

/**************************************************/
/* Pass in a datetime and it will return the date */
/* portion with the time set to 00:00:00          */
/**************************************************/
CREATE FUNCTION fn_Midnight (@CheckDate datetime)  
RETURNS datetime AS  
BEGIN 

	-- Strip out the date and rebuild, kind of a bionic date - we can rebuild it!
	Declare @NewDateTime datetime

	Set @NewDateTime = DateAdd(yyyy, Year(@CheckDate) - 1900, 0)
	Set @NewDateTime = DateAdd(mm, Month(@CheckDate) - 1, @NewDateTime)
	Set @NewDateTime = DateAdd(dd, Day(@CheckDate) - 1, @NewDateTime)

	Return @NewDateTime

END


/**************************************************/
/* Pass in an offset of days                      */
/* -1 = yesterday, 1 is tomorrow etc              */
/* and it will return the date that number of     */
/* working days before or after the date specified*/
/* by the second parameter                        */
/**************************************************/
CREATE FUNCTION fn_WorkingDaysDiff(@Offset int, @TheDate datetime)
RETURNS DATETIME
AS
BEGIN
	Declare @ReturnDate datetime

	-- Drop the date to midnight
	Set @TheDate = dbo.fn_Midnight(@TheDate)

	If @Offset = 0
	Begin
		Set @ReturnDate = @TheDate
	End
	Else If @Offset < 0
	Begin
		If (DatePart(dw, @TheDate)-1) > Abs(@Offset)
		Begin
			Set @ReturnDate = DateAdd(d, @Offset, @TheDate)
		End
		Else
		Begin
			-- Correct for sundays
			If DatePart(dw, @TheDate) = 1 Set @Offset = @Offset + 1
			Set @ReturnDate = DateAdd(d, @OffSet + 2 * Cast((DatePart(dw, @TheDate) + @OffSet - 1)/5-1 as integer), @TheDate)
		End
	End
	Else If @Offset > -1
		Begin
		If (DatePart(dw, @TheDate) + @Offset) < 7
		Begin
			Set @ReturnDate = DateAdd(d, @Offset, @TheDate)
		End
		Else
		Begin
			-- Correct for Saturday error		
			If DatePart(dw, @TheDate) = 7 Set @Offset = @Offset - 1
			Set @ReturnDate = DateAdd(d, Cast((DatePart(dw, @TheDate) + @Offset - 2) /5 as integer)*2 + @Offset, @TheDate)
		End
	End

	Return @ReturnDate

END

Rate

Share

Share

Rate