Count work days between two dates in T-SQL

Question

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

Monday to Friday and it must be T-SQL.

Answer 1

Can you define workdays? any Monday through friday? Excluding major holidays? What country? Must it be done in SQL?

Answer 2

Hi,

Here you can find good article about this subject, but as you can see it is not that advanced.

--Changing current database to the Master database allows function to be shared by everyone
USE MASTER
GO
--If the function already exists, drop it
IF EXISTS
(
    SELECT *
    FROM dbo.SYSOBJECTS
    WHERE ID = OBJECT_ID(N'[dbo].[fn_WorkDays]')
    AND XType IN (N'FN', N'IF', N'TF')
)
DROP FUNCTION [dbo].[fn_WorkDays]
GO
 CREATE FUNCTION dbo.fn_WorkDays
--Presets
--Define the input parameters (ok if reversed by mistake)
(
    @StartDate DATETIME,
    @EndDate   DATETIME = NULL --@EndDate replaced by @StartDate when DEFAULTed
)

--Define the output data type
RETURNS INT

AS
--Calculate the RETURN of the function
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 > @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
GO

If you need to use a custom calendar, you might need to add some checks and some parameters. Hopefully it will provide a good starting point.

Answer 3

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...

Answer 4

Check out this URL http://csharpwiki.wordpress.com/ for Count Business Days between Two Dates in SQL

Answer 5

DECLARE @TotalDays INT,@WorkDays INT DECLARE @ReducedDayswithEndDate INT DECLARE @WeekPart INT DECLARE @DatePart INT

SET @TotalDays= DATEDIFF(day, @StartDate, @EndDate) +1 SELECT @ReducedDayswithEndDate = CASE DATENAME(weekday, @EndDate) WHEN 'Saturday' THEN 1 WHEN 'Sunday' THEN 2 ELSE 0 END SET @TotalDays=@TotalDays-@ReducedDayswithEndDate SET @WeekPart=@TotalDays/7; SET @DatePart=@TotalDays%7; SET @WorkDays=(@WeekPart*5)+@DatePart

RETURN @WorkDays

Answer 6

DECLARE @StartDate datetime,@EndDate datetime

select @StartDate='3/2/2010', @EndDate='3/7/2010'

DECLARE @TotalDays INT,@WorkDays INT

DECLARE @ReducedDayswithEndDate INT

DECLARE @WeekPart INT

DECLARE @DatePart INT

SET @TotalDays= DATEDIFF(day, @StartDate, @EndDate) +1

SELECT @ReducedDayswithEndDate = CASE DATENAME(weekday, @EndDate) WHEN 'Saturday' THEN 1 WHEN 'Sunday' THEN 2 ELSE 0 END

SET @TotalDays=@TotalDays-@ReducedDayswithEndDate

SET @WeekPart=@TotalDays/7;

SET @DatePart=@TotalDays%7;

SET @WorkDays=(@WeekPart*5)+@DatePart

SELECT @WorkDays