Just thought I'd share this "brilliant" bit of SQL code I saw recently. You see, it's supposed to add x work-days to the given date. From looking at the size of it, though, I thought it would also boil me a pot of water, wash my dishes and give me a back massage. Some variable names changed to protect the "innocent".
CREATE FUNCTION udf_AddWorkDays
( @Date datetime,
@Days int )
RETURNS datetime
AS
BEGIN
DECLARE @TotalDays int, @FirstPart int
DECLARE @EndDate datetime
DECLARE @LastNum int, @LastPart int
IF @Days < 0
BEGIN
SELECT @FirstPart = CASE DATENAME(weekday, @Date)
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(@Days) < @FirstPart
SELECT @EndDate = DATEADD(dd, @Days, @Date)
ELSE
BEGIN
SELECT @TotalDays = (ABS(@Days) - @FirstPart) / 5
SELECT @LastPart = (ABS(@Days) - @FirstPart) % 7
SELECT @LastNum = CASE
WHEN (@LastPart < 7) AND (@LastPart > 0) THEN @LastPart - 1
ELSE 0
END
SELECT @TotalDays = - 2 * (@TotalDays + 1) + @Days
SELECT @EndDate = DATEADD(dd, @TotalDays, @Date)
END
END
ELSE
BEGIN
SELECT @FirstPart = CASE DATENAME(weekday, @Date)
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 @Days < @FirstPart
SELECT @EndDate = DATEADD(dd, @Days, @Date)
ELSE
BEGIN
SELECT @TotalDays = (@Days - @FirstPart) / 5
SELECT @LastPart = (@Days - @FirstPart) % 7
SELECT @LastNum = CASE
WHEN (@LastPart < 7) AND (@LastPart > 0) THEN @LastPart - 1
ELSE 0
END
SELECT @TotalDays = 2 * (@TotalDays + 1) + @Days
SELECT @EndDate = DATEADD(dd, @TotalDays, @Date)
END
END
RETURN ( @EndDate )
END