title: Calculating how many Working Days between 2 Dates - T-SQL?
body: <p>I realise different solutions will have different variations of what "Working Days" means but in my case I mean Monday to Friday inclusive.</p>
  
  <p>Basically I have Created a function to do the calculation for me and my current solution works. My concern (and reason for asking this question) is that I am worried that this is a bad way of achieving this because the function is being called with a very high frequency. In the last 3 months it has been called 12 million times on a production system, with the average worker time 44ms.</p>
  
  <p>This lead me to wonder if this is the correct way of achieving solution.</p>
  
  <p>Firstly here is the function I created:</p>
  
  <pre><code> CREATE FUNCTION [dbo].[fn_WorkDays]
      (
       @StartDate DATETIME,
       @EndDate   DATETIME = NULL --@EndDate replaced by @StartDate when DEFAULTed
      )
      RETURNS INT
   AS
  
   BEGIN
      --===== Declare local variables
      --Temporarily holds @EndDate during date reversal
      DECLARE @Swap DATETIME
  
      --===== If the Start Date is null, return a NULL and exit
           IF @StartDate IS NULL
              RETURN NULL
  
      --===== If the End Date is null, populate with Start Date value
           -- so will have two dates (required by DATEDIFF below)
           IF @EndDate IS NULL
              SELECT @EndDate = @StartDate
  
      --===== Strip the time element from both dates (just to be safe) by converting
           -- to whole days and back to a date.  Usually faster than CONVERT.
           -- 0 is a date (01/01/1900 00:00:00.000)
       SELECT @StartDate = DATEADD(dd,DATEDIFF(dd,0,@StartDate),0),
              @EndDate   = DATEADD(dd,DATEDIFF(dd,0,@EndDate)  ,0)
  
      --===== If the inputs are in the wrong order, reverse them
           IF @StartDate &gt; @EndDate
              SELECT @Swap      = @EndDate,
                     @EndDate   = @StartDate,
                     @StartDate = @Swap
  
      --===== Calculate and return the number of workdays using the
           -- input parameters.  This is the meat of the function.
           -- This is really just one formula with a couple of parts
           -- that are listed on separate lines for documentation
           -- purposes.
       RETURN (
              SELECT
            --Start with total number of days including weekends
              (DATEDIFF(dd,@StartDate,@EndDate)+1)
  
            --Subtact 2 days for each full weekend
             -(DATEDIFF(wk,@StartDate,@EndDate)*2)
  
            --If StartDate is a Sunday, Subtract 1
             -(CASE WHEN DATENAME(dw,@StartDate) = 'Sunday'
                    THEN 1
                    ELSE 0
                END)
  
            --If EndDate is a Saturday, Subtract 1
             -(CASE WHEN DATENAME(dw,@EndDate) = 'Saturday'
                    THEN 1
                    ELSE 0
                END)
              )
  END
  </code></pre>
  
  <p>As a simple example of its use I would run this type of query:</p>
  
  <pre><code>SELECT MYTABLE.EntryDate
       ,dbo.fn_WorkDays(MYTABLE.EntryDate, getutcdate()) as WorkingDays
      FROM MYTABLE                                    
  </code></pre>
  
  <p>MyTable could contain 5000 rows all with different Dates in the EntryDate Column (5000 calls to Function)</p>
  
  <p>My question is I am missing something here in the way that I am doing this, would it be beneficial to create a lookup table for this (but that is a lot of combinations of dates)</p>
  
  <p>Any thoughts, improvements or recommendations would be appreciated...</p>