SQL - Find working hours between 2 dates excluding weekends

Question

I'm working on a code to get net working days excluding weekends (saturday and sunday). I need this data in days. I've written following 2 functions for it

The first function returns the start time of the date

ALTER FUNCTION day_start
   (@dd as datetime)
   returns datetime
   begin
     return cast(floor(cast(@dd as float)) as datetime)
   end

The second one does the actual work

alter function networkingdays_hours (@startdate as datetime, @enddate as datetime)
returns float
begin
return
(

SELECT
    cast(
    (DATEDIFF(dd,@StartDate, @EndDate))
    -(DATEDIFF(wk,@StartDate, @EndDate)*2)
    -(CASE WHEN DATENAME(dw, @StartDate) ='Sunday'
      THEN 1
            ELSE 0
        END)
    -(CASE WHEN DATENAME(dw, @EndDate) ='Saturday'
      THEN 1
            ELSE 0
        END)        
    +(@EndDate - dbo.day_start(@EndDate))
        -(@startdate - dbo.day_start(@startdate))
         as float))
    END

Example start time - 8/31/2012 9:22:00 AM, End Time - 9/1/2012 7:14:00 AM, Expected result - 0.911111111, Code output - (-0.08888888)

Please help.

Well if the data indicates they finish before they start, what do you expect? — podiluska, Aug 28 '13 at 16:53
Looks sorta like this question: http://stackoverflow.com/q/7444846/192510 — NealB, Aug 28 '13 at 19:24
I'm not sure if it's the best idea, but my first instinct is to make a lookup table of every weekend and holiday date. Then your function can loop thru every date in your range comparing it to the lookup table. On the plus side, you can now exclude company-specific holidays as well as the weekends. But on the downside, you have to maintain that lookup table on a yearly basis. — PowerUser, Aug 28 '13 at 19:40

score 0 · Answer 1 · answered Aug 29 '13 at 07:43

Your function doesn't work in this specific case because Sept 1 is a weekend, and you have indicated that 1 should be subtracted from the result if that is the case.

In the general case, it doesn't work, because there will be an increasing number of special and edge cases that require ever more convoluted logic.

Creating a working days table is a much more flexible solution

SQL - Find working hours between 2 dates excluding weekends

1 Answers1