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".
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
-
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:
Just thought I'd share this "brilliant" bit of SQL code
The true tragedy of this site is that I now reflexively parse "brillant" as the correctly-spelled version.
-
Really? Really no comments at all? (I accidentally clicked on the "Report Abuse" link instead of "Reply" for this message.)
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:
Really? Really no comments at all? (I accidentally clicked on the "Report Abuse" link instead of "Reply" for this message.)
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 :)