locked
Calculating difference between two dates (in workdays) RRS feed

  • Question

  • Can I calculate difference in workdays between two dates in an easy way? Is there a built in function in SQL Server that’ll work?
    Thursday, May 30, 2013 9:28 AM

Answers

All replies

  • Here is a piece of code that you can just implement into a function or whatever you need:

    declare @Begin datetime
    declare @End datetime
    
    declare @TotalDays int
    declare @NumberOfWeekends int
    
    SET @Begin = '2/16/2013'
    SET @End = '6/27/2013'
    SET @NumberOfWeekends = 0
    SET @TotalDays = DATEDIFF(d,@ Begin, @End)
    
    If @TotalDays > 7
        SET @NumberOfWeekends = @TotalDays / 7
    else if DATEPART(dd, @End) < DATEPART(DD, @ Begin)
        SET @NumberOfWeekends = 1
    
    select @TotalDays - 2*@WeekendsNumber)
    • Proposed as answer by Fanny Liu Wednesday, June 5, 2013 9:47 AM
    Thursday, May 30, 2013 9:33 AM
  • Take a look at DATEDIFF system function

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Thursday, May 30, 2013 9:53 AM
    Answerer
  • Create the scalar-value function fnWorkdaysInclusive with the following T-SQL:

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
     
    -- =========================================
    -- Author: Van Dinh
    -- Note: Both @StartDate and @EndDate are included 
    --   if they are workdays
    -- =========================================
    CREATE FUNCTION dbo.fnWeekDaysInclusive 
    ( 
      @StartDate datetime,
      @EndDate datetime
    )
    RETURNS int
    AS
    BEGIN
     
    DECLARE @ResultVar int
     
    SELECT @ResultVar =   
      (DATEDIFF(dd, @StartDate, @EndDate) + 1)  
     -(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) 
         
    -- Return the result of the function
    RETURN @ResultVar
     
    END
    GO

     

    Once the function is created, you can use it like:

    SELECT dbo.fnWeekDaysInclusive('2013/05/11', '2013/05/24') 
      AS InclusiveWorkdays

     


    Van Dinh

    Thursday, May 30, 2013 12:09 PM
  • Create a calender and use JOIN to get the desired result.

    Go through the thread, http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/fd3e0b0c-e799-4ed9-bfbd-37963cecf930

    Few more informative links,

    SQL 2005 and below -http://www.made2mentor.com/2011/04/calendar-tables-why-you-need-one/

    SQL 2008 and + --http://www.made2mentor.com/2011/06/creating-a-date-tabledimension-for-sql-server-2008/


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    Thursday, May 30, 2013 1:51 PM
    Answerer
  • You will find that there is no generic solution since the definition of "work days" varies widely.   By far, the most effective method is that suggested by Sarat - use a calendar table. 
    Thursday, May 30, 2013 2:15 PM
    Answerer