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)?


  • Considered Harmful

    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 :)


Log in to reply