sqlt-sqldate

Count work days between two dates


How can I calculate the number of work days between two dates in SQL Server?

Monday to Friday and it must be T-SQL.


Solution

  • For workdays, Monday to Friday, you can do it with a single SELECT, like this:

    DECLARE @StartDate DATETIME
    DECLARE @EndDate DATETIME
    SET @StartDate = '2008/10/01'
    SET @EndDate = '2008/10/31'
    
    
    SELECT
       (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)
    

    If you want to include holidays, you have to work it out a bit...