SQL Server BUSDAYS Function

Updated 2023-10-06 23:25:11.657000

Description

Calculate the number of business days from a start date (inclusive) to an end date (exclusive).

Syntax

SELECT [westclintech].[wct].[BUSDAYS] (
  <@StartDate, datetime,>
 ,<@EndDate, datetime,>
 ,<@Holidays, nvarchar(max),>)

Arguments

@StartDate

the start date for the calculation. @StartDate is an expression of type datetime or of a type that can be implicitly converted to datetime.

@EndDate

the end date for the calculation. @EndDate is an expression of type datetime or of a type that can be implicitly converted to datetime.

@Holidays

a comma separated string containing the holiday (non-business) dates to be used in the calculation of the number of business days. You can use the aggregate function NBD NBD to create an appropriately formatted string.

Return Type

int

Remarks

@StartDate cannot be NULL.

@EndDate cannot be NULL.

Saturday and Sunday are always treated as non-business days.

Examples

In this example we will put the holiday string into a variable, using the NBD function and calculate the number of business days and the actual number of days between different date combinations.

/*Populate the variable*/
DECLARE @h as varchar(max);
SET @h =
(
    SELECT wct.NBD(d)
    FROM
    (
        SELECT '2010-01-01'
        UNION ALL
        SELECT '2010-01-18'
        UNION ALL
        SELECT '2010-02-15'
        UNION ALL
        SELECT '2010-05-31'
        UNION ALL
        SELECT '2010-07-05'
        UNION ALL
        SELECT '2010-09-06'
        UNION ALL
        SELECT '2010-10-11'
        UNION ALL
        SELECT '2010-11-25'
        UNION ALL
        SELECT '2010-12-24'
        UNION ALL
        SELECT '2010-12-31'
        UNION ALL
        SELECT '2011-01-17'
        UNION ALL
        SELECT '2011-02-21'
        UNION ALL
        SELECT '2011-05-30'
        UNION ALL
        SELECT '2011-07-04'
        UNION ALL
        SELECT '2011-09-05'
        UNION ALL
        SELECT '2011-10-10'
        UNION ALL
        SELECT '2011-10-11'
        UNION ALL
        SELECT '2011-11-24'
        UNION ALL
        SELECT '2011-12-26'
        UNION ALL
        SELECT '2012-01-02'
        UNION ALL
        SELECT '2012-01-16'
        UNION ALL
        SELECT '2012-02-20'
        UNION ALL
        SELECT '2012-05-28'
        UNION ALL
        SELECT '2012-07-04'
        UNION ALL
        SELECT '2012-09-03'
        UNION ALL
        SELECT '2012-10-08'
        UNION ALL
        SELECT '2012-11-12'
        UNION ALL
        SELECT '2012-11-22'
        UNION ALL
        SELECT '2012-12-25'
        UNION ALL
        SELECT '2013-01-01'
        UNION ALL
        SELECT '2013-01-21'
        UNION ALL
        SELECT '2013-02-18'
        UNION ALL
        SELECT '2013-05-27'
        UNION ALL
        SELECT '2013-07-04'
        UNION ALL
        SELECT '2013-09-02'
        UNION ALL
        SELECT '2013-10-14'
        UNION ALL
        SELECT '2013-11-11'
        UNION ALL
        SELECT '2013-11-28'
        UNION ALL
        SELECT '2013-12-25'
        UNION ALL
        SELECT '2014-01-01'
        UNION ALL
        SELECT '2014-01-20'
        UNION ALL
        SELECT '2014-02-17'
        UNION ALL
        SELECT '2014-05-26'
        UNION ALL
        SELECT '2014-07-04'
        UNION ALL
        SELECT '2014-09-01'
        UNION ALL
        SELECT '2014-10-13'
        UNION ALL
        SELECT '2014-11-11'
        UNION ALL
        SELECT '2014-11-27'
        UNION ALL
        SELECT '2014-12-25'
        UNION ALL
        SELECT '2015-01-01'
        UNION ALL
        SELECT '2015-01-19'
        UNION ALL
        SELECT '2015-02-16'
        UNION ALL
        SELECT '2015-05-25'
        UNION ALL
        SELECT '2015-07-03'
        UNION ALL
        SELECT '2015-09-07'
        UNION ALL
        SELECT '2015-10-12'
        UNION ALL
        SELECT '2015-11-11'
        UNION ALL
        SELECT '2015-11-26'
        UNION ALL
        SELECT '2015-12-25'
    ) n(d)
);

Calculate business days using the previously declared variable.

SELECT *,
       wct.BUSDAYS([Start Date], [End Date], @h) as [Business Days],
       DATEDIFF(d, [Start Date], [End Date]) as Actual
FROM
(
    SELECT '2015-08-01',
           '2014-11-19'
    UNION ALL
    SELECT '2014-11-01',
           '2015-04-12'
    UNION ALL
    SELECT '2010-03-16',
           '2015-12-12'
    UNION ALL
    SELECT '2009-10-21',
           '2011-11-24'
    UNION ALL
    SELECT '2014-07-22',
           '2015-10-13'
    UNION ALL
    SELECT '2013-02-03',
           '2012-02-27'
    UNION ALL
    SELECT '2013-04-26',
           '2015-01-23'
    UNION ALL
    SELECT '2013-01-07',
           '2011-06-07'
    UNION ALL
    SELECT '2015-08-04',
           '2010-09-23'
    UNION ALL
    SELECT '2013-12-16',
           '2015-08-25'
    UNION ALL
    SELECT '2015-01-02',
           '2013-08-07'
    UNION ALL
    SELECT '2009-12-02',
           '2011-10-07'
    UNION ALL
    SELECT '2014-06-14',
           '2015-10-23'
    UNION ALL
    SELECT '2010-01-15',
           '2015-03-13'
    UNION ALL
    SELECT '2015-01-19',
           '2010-06-07'
    UNION ALL
    SELECT '2014-09-20',
           '2012-12-31'
    UNION ALL
    SELECT '2009-11-08',
           '2014-09-01'
    UNION ALL
    SELECT '2011-07-28',
           '2015-09-24'
    UNION ALL
    SELECT '2010-11-07',
           '2011-06-08'
    UNION ALL
    SELECT '2013-01-08',
           '2014-11-26'
    UNION ALL
    SELECT '2012-07-12',
           '2015-02-04'
    UNION ALL
    SELECT '2012-07-03',
           '2012-08-30'
) m([Start Date], [End Date]);

This produces the following result.

{"columns":[{"field":"Start Date","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"End Date","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Business Days","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Actual","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Start Date":"2015-08-01","End Date":"2014-11-19","Business Days":"-176","Actual":"-255"},{"Start Date":"2014-11-01","End Date":"2015-04-12","Business Days":"109","Actual":"162"},{"Start Date":"2010-03-16","End Date":"2015-12-12","Business Days":"1444","Actual":"2097"},{"Start Date":"2009-10-21","End Date":"2011-11-24","Business Days":"529","Actual":"764"},{"Start Date":"2014-07-22","End Date":"2015-10-13","Business Days":"308","Actual":"448"},{"Start Date":"2013-02-03","End Date":"2012-02-27","Business Days":"-236","Actual":"-342"},{"Start Date":"2013-04-26","End Date":"2015-01-23","Business Days":"436","Actual":"637"},{"Start Date":"2013-01-07","End Date":"2011-06-07","Business Days":"-397","Actual":"-580"},{"Start Date":"2015-08-04","End Date":"2010-09-23","Business Days":"-1220","Actual":"-1776"},{"Start Date":"2013-12-16","End Date":"2015-08-25","Business Days":"425","Actual":"617"},{"Start Date":"2015-01-02","End Date":"2013-08-07","Business Days":"-351","Actual":"-513"},{"Start Date":"2009-12-02","End Date":"2011-10-07","Business Days":"467","Actual":"674"},{"Start Date":"2014-06-14","End Date":"2015-10-23","Business Days":"341","Actual":"496"},{"Start Date":"2010-01-15","End Date":"2015-03-13","Business Days":"1294","Actual":"1883"},{"Start Date":"2015-01-19","End Date":"2010-06-07","Business Days":"-1159","Actual":"-1687"},{"Start Date":"2014-09-20","End Date":"2012-12-31","Business Days":"-434","Actual":"-628"},{"Start Date":"2009-11-08","End Date":"2014-09-01","Business Days":"1211","Actual":"1758"},{"Start Date":"2011-07-28","End Date":"2015-09-24","Business Days":"1044","Actual":"1519"},{"Start Date":"2010-11-07","End Date":"2011-06-08","Business Days":"146","Actual":"213"},{"Start Date":"2013-01-08","End Date":"2014-11-26","Business Days":"474","Actual":"687"},{"Start Date":"2012-07-12","End Date":"2015-02-04","Business Days":"642","Actual":"937"},{"Start Date":"2012-07-03","End Date":"2012-08-30","Business Days":"41","Actual":"58"}]}

Let's assume that we keep holidays on a table indexed by country code in our database. We could run the following SQL using CROSS APPLY to supply holiday information to the function.

SELECT [Start Date]
,[End Date]
,wct.BUSDAYS([Start Date],[End Date],h) as [Business Days]
,DATEDIFF(d,[Start Date],[End Date]) as Actual
FROM (
      SELECT '2015-08-01','2014-11-19' UNION ALL
      SELECT '2014-11-01','2015-04-12' UNION ALL
      SELECT '2010-03-16','2015-12-12' UNION ALL
      SELECT '2009-10-21','2011-11-24' UNION ALL
      SELECT '2014-07-22','2015-10-13' UNION ALL
      SELECT '2013-02-03','2012-02-27' UNION ALL
      SELECT '2013-04-26','2015-01-23' UNION ALL
      SELECT '2013-01-07','2011-06-07' UNION ALL
      SELECT '2015-08-04','2010-09-23' UNION ALL
      SELECT '2013-12-16','2015-08-25' UNION ALL
      SELECT '2015-01-02','2013-08-07' UNION ALL
      SELECT '2009-12-02','2011-10-07' UNION ALL
      SELECT '2014-06-14','2015-10-23' UNION ALL
      SELECT '2010-01-15','2015-03-13' UNION ALL
      SELECT '2015-01-19','2010-06-07' UNION ALL
      SELECT '2014-09-20','2012-12-31' UNION ALL
      SELECT '2009-11-08','2014-09-01' UNION ALL
      SELECT '2011-07-28','2015-09-24' UNION ALL
      SELECT '2010-11-07','2011-06-08' UNION ALL
      SELECT '2013-01-08','2014-11-26' UNION ALL
      SELECT '2012-07-12','2015-02-04' UNION ALL
      SELECT '2012-07-03','2012-08-30'
      ) m([Start Date],[End Date])
CROSS APPLY (
      SELECT wct.NBD(Holiday) FROM HOLIDAYS
      WHERE Country = 'US'
      ) k(h)

This produces the following result.

{"columns":[{"field":"Start Date","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"End Date","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Business Days","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Actual","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Start Date":"2015-08-01","End Date":"2014-11-19","Business Days":"-175","Actual":"-255"},{"Start Date":"2014-11-01","End Date":"2015-04-12","Business Days":"108","Actual":"162"},{"Start Date":"2010-03-16","End Date":"2015-12-12","Business Days":"1443","Actual":"2097"},{"Start Date":"2009-10-21","End Date":"2011-11-24","Business Days":"528","Actual":"764"},{"Start Date":"2014-07-22","End Date":"2015-10-13","Business Days":"308","Actual":"448"},{"Start Date":"2013-02-03","End Date":"2012-02-27","Business Days":"-235","Actual":"-342"},{"Start Date":"2013-04-26","End Date":"2015-01-23","Business Days":"436","Actual":"637"},{"Start Date":"2013-01-07","End Date":"2011-06-07","Business Days":"-397","Actual":"-580"},{"Start Date":"2015-08-04","End Date":"2010-09-23","Business Days":"-1220","Actual":"-1776"},{"Start Date":"2013-12-16","End Date":"2015-08-25","Business Days":"425","Actual":"617"},{"Start Date":"2015-01-02","End Date":"2013-08-07","Business Days":"-351","Actual":"-513"},{"Start Date":"2009-12-02","End Date":"2011-10-07","Business Days":"467","Actual":"674"},{"Start Date":"2014-06-14","End Date":"2015-10-23","Business Days":"341","Actual":"496"},{"Start Date":"2010-01-15","End Date":"2015-03-13","Business Days":"1294","Actual":"1883"},{"Start Date":"2015-01-19","End Date":"2010-06-07","Business Days":"-1158","Actual":"-1687"},{"Start Date":"2014-09-20","End Date":"2012-12-31","Business Days":"-433","Actual":"-628"},{"Start Date":"2009-11-08","End Date":"2014-09-01","Business Days":"1210","Actual":"1758"},{"Start Date":"2011-07-28","End Date":"2015-09-24","Business Days":"1044","Actual":"1519"},{"Start Date":"2010-11-07","End Date":"2011-06-08","Business Days":"146","Actual":"213"},{"Start Date":"2013-01-08","End Date":"2014-11-26","Business Days":"474","Actual":"687"},{"Start Date":"2012-07-12","End Date":"2015-02-04","Business Days":"642","Actual":"937"},{"Start Date":"2012-07-03","End Date":"2012-08-30","Business Days":"41","Actual":"58"}]}

Let's look at what happens if the start date is a Saturday.

DECLARE @sd as datetime;
DECLARE @h as varchar(max);
SET @sd = '2012-05-26';
SET @h =
(
    SELECT wct.NBD(holiday)FROM HOLIDAYS WHERE COUNTRY = 'US'
);
SELECT RIGHT(Convert(varchar, @sd, 106), 11) as [Start Date],
       RIGHT(Convert(varchar, seriesvalue, 106), 11) as [End Date],
       wct.BUSDAYS(@sd, seriesvalue, @h) as [Business Days],
       DATEDIFF(d, @sd, seriesvalue) as [Actual Days]
FROM wctMath.wct.SeriesDate(@sd, '2012-06-06', NULL, NULL, NULL);

This produces the following result.

{"columns":[{"field":"Start Date","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"End Date","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Business Days","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Actual Days","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Start Date":"26 May 2012","End Date":"26 May 2012","Business Days":"0","Actual Days":"0"},{"Start Date":"26 May 2012","End Date":"27 May 2012","Business Days":"0","Actual Days":"1"},{"Start Date":"26 May 2012","End Date":"28 May 2012","Business Days":"0","Actual Days":"2"},{"Start Date":"26 May 2012","End Date":"29 May 2012","Business Days":"0","Actual Days":"3"},{"Start Date":"26 May 2012","End Date":"30 May 2012","Business Days":"1","Actual Days":"4"},{"Start Date":"26 May 2012","End Date":"31 May 2012","Business Days":"2","Actual Days":"5"},{"Start Date":"26 May 2012","End Date":"01 Jun 2012","Business Days":"3","Actual Days":"6"},{"Start Date":"26 May 2012","End Date":"02 Jun 2012","Business Days":"3","Actual Days":"7"},{"Start Date":"26 May 2012","End Date":"03 Jun 2012","Business Days":"3","Actual Days":"8"},{"Start Date":"26 May 2012","End Date":"04 Jun 2012","Business Days":"4","Actual Days":"9"},{"Start Date":"26 May 2012","End Date":"05 Jun 2012","Business Days":"5","Actual Days":"10"},{"Start Date":"26 May 2012","End Date":"06 Jun 2012","Business Days":"6","Actual Days":"11"}]}

Notice that is not until 30-May-2012 that one business day has elapsed. There are 4 actual days from 26-May-2012 to 30-May-2012. 26-May-2012 is a Saturday, 27-May-2012 is a Sunday, and 28-May-2012 is holiday. Of the 4 days, 3 are non-business days, so the result is 1 business day.

Let's look at what happens if the end date is a Sunday.

DECLARE @ed as datetime;
DECLARE @h as varchar(max);
SET @ed = '2012-06-03';
SET @h =
(
    SELECT wct.NBD(holiday)FROM HOLIDAYS WHERE COUNTRY = 'US'
);
SELECT RIGHT(Convert(varchar, seriesvalue, 106), 11) as [Start Date],
       RIGHT(Convert(varchar, @ed, 106), 11) as [End Date],
       wct.BUSDAYS(seriesvalue, @ed, @h) as [Business Days],
       DATEDIFF(d, seriesvalue, @ed) as [Actual Days]
FROM wctMath.wct.SeriesDate('2012-05-23', @ed, NULL, NULL, NULL);

This produces the following result.

{"columns":[{"field":"Start Date","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"End Date","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Business Days","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Actual Days","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Start Date":"23 May 2012","End Date":"03 Jun 2012","Business Days":"6","Actual Days":"11"},{"Start Date":"24 May 2012","End Date":"03 Jun 2012","Business Days":"5","Actual Days":"10"},{"Start Date":"25 May 2012","End Date":"03 Jun 2012","Business Days":"4","Actual Days":"9"},{"Start Date":"26 May 2012","End Date":"03 Jun 2012","Business Days":"3","Actual Days":"8"},{"Start Date":"27 May 2012","End Date":"03 Jun 2012","Business Days":"3","Actual Days":"7"},{"Start Date":"28 May 2012","End Date":"03 Jun 2012","Business Days":"3","Actual Days":"6"},{"Start Date":"29 May 2012","End Date":"03 Jun 2012","Business Days":"3","Actual Days":"5"},{"Start Date":"30 May 2012","End Date":"03 Jun 2012","Business Days":"2","Actual Days":"4"},{"Start Date":"31 May 2012","End Date":"03 Jun 2012","Business Days":"1","Actual Days":"3"},{"Start Date":"01 Jun 2012","End Date":"03 Jun 2012","Business Days":"0","Actual Days":"2"},{"Start Date":"02 Jun 2012","End Date":"03 Jun 2012","Business Days":"0","Actual Days":"1"},{"Start Date":"03 Jun 2012","End Date":"03 Jun 2012","Business Days":"0","Actual Days":"0"}]}

Notice that there 11 actual days between 23-May-2012 and 03-Jun-2012, but there are only 6 business days. There are two Saturdays (26-May-2012 and 02-Jun-2012), two Sundays (27-May-2012 and 03-Jun-2012) and one holiday (28-May-2012).

See Also

BUSINESSDATE - Calculate a Business Date from an offset

BUSINESSDATEWE - Calculate a Business Date from an offset and specified weekend days

FIRSTWEEKDAY - Calculate the last occurrence of a weekday in a given month

LASTWEEKDAY - Calculate the first occurrence of a weekday in a given month

NETWORKDAYS - Calculate the number of working days between two dates (inclusive)

NBD - Create holiday string

YEARFRAC - Calculate the difference between two dates as a fraction of a year