CREATE FUNCTION dbo.ADDWorkingDays
( @StartDate datetime,
@WorkDays int )
RETURNS datetime
AS
BEGIN
DECLARE @TotalDays int, @FirstPart int
DECLARE @EndDate datetime
DECLARE @LastNum int, @LastPart int
IF @WorkDays < 0
BEGIN
SELECT @FirstPart = CASE DATENAME(weekday, @StartDate)
WHEN 'Sunday' THEN 0
WHEN 'Monday' THEN 1
WHEN 'Tuesday' THEN 2
WHEN 'Wednesday' THEN 3
WHEN 'Thursday' THEN 4
WHEN 'Friday' THEN 5
WHEN 'Saturday' THEN 6
END
IF ABS(@WorkDays) < @FirstPart
SELECT @EndDate = DATEADD(dd, @WorkDays, @StartDate)
ELSE
BEGIN
SELECT @TotalDays = (ABS(@WorkDays) - @FirstPart) / 5
SELECT @LastPart = (ABS(@WorkDays) - @FirstPart) % 7
SELECT @LastNum = CASE
WHEN (@LastPart < 7) AND (@LastPart > 0) THEN @LastPart - 1
ELSE 0
END
SELECT @TotalDays = - 2 * (@TotalDays + 1) + @WorkDays
SELECT @EndDate = DATEADD(dd, @TotalDays, @StartDate)
END
END
ELSE
BEGIN
SELECT @FirstPart = CASE DATENAME(weekday, @StartDate)
WHEN 'Sunday' THEN 6
WHEN 'Monday' THEN 5
WHEN 'Tuesday' THEN 4
WHEN 'Wednesday' THEN 3
WHEN 'Thursday' THEN 2
WHEN 'Friday' THEN 1
WHEN 'Saturday' THEN 0
END
IF @WorkDays < @FirstPart
SELECT @EndDate = DATEADD(dd, @WorkDays, @StartDate)
ELSE
BEGIN
SELECT @TotalDays = (@WorkDays - @FirstPart) / 5
SELECT @LastPart = (@WorkDays - @FirstPart) % 7
SELECT @LastNum = CASE
WHEN (@LastPart < 7) AND (@LastPart > 0) THEN @LastPart - 1
ELSE 0
END
SELECT @TotalDays = 2 * (@TotalDays + 1) + @WorkDays
SELECT @EndDate = DATEADD(dd, @TotalDays, @StartDate)
END
END
RETURN ( @EndDate )
END
GO
|
