Count work days between two dates

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

DECLARE @StartDate DATETIMEDECLARE @EndDate DATETIMESET @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...

In Calculating Work Days you can find a 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 MASTERGO--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 INTAS--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)        )    ENDGO

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.

All Credit to Bogdan Maxim & Peter Mortensen. This is their post, I just added holidays to the function (This assumes you have a table "tblHolidays" with a datetime field "HolDate".

--Changing current database to the Master database allows function to be shared by everyone.USE MASTERGO--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 INTAS--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)        --Subtract all holidays        -(Select Count(*) from [DB04\DB04].[Gateway].[dbo].[tblHolidays]          where  [HolDate] between @StartDate and @EndDate )        )    END  GO-- Test Script/*declare @EndDate datetime= dateadd(m,2,getdate())print @EndDateselect  [Master].[dbo].[fn_WorkDays] (getdate(), @EndDate)*/