# How to implement simple date-time calculations

• 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".

( @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

• What about when I want to go to another calendar and add an eigth day (as in: Eight Days a Week)?

• What a WTF!  I mean, it doesn't even check for federal holidays.  Are we to work through Christmas?

• @NickAragua said:

The true tragedy of this site is that I now reflexively parse "brillant" as the correctly-spelled version.

I only got as far as this line:

IF ABS(@Days) < @FirstPart SELECT @EndDate = DATEADD(dd, @Days, @Date)

So if @Date is 2007-08-31 (Friday==5), and @Days is 1, this function returns 2007-09-01 (Saturday)....did I miss something, or is this [part of] the WTF?

Also, why are the days of the week numbered backward in the 2nd part? <Head hurts>

• You missed something. The first part is for adding a NEGATIVE number of days. The numbers aren't the weekday day number, but the max you can add/subtract before you hit the end of the week. For Days=1 and Date=Friday (FirstPart=1) it fails the test of Days<FirstPart and does its rollover calculations.

• @savar said:

I only got as far as this line:

IF ABS(@Days) < @FirstPart SELECT @EndDate = DATEADD(dd, @Days, @Date)

So if @Date is 2007-08-31 (Friday==5), and @Days is 1, this function returns 2007-09-01 (Saturday)....did I miss something, or is this [part of] the WTF?

Also, why are the days of the week numbered backward in the 2nd part? <Head hurts>

Maybe because you are looking at the wrong code, the ABS(@Days) part is only when @Days < 0

• He posted over 16 hours ago, and we both correct him within 5 minutes of each other

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.