BUSDAYS

Updated 2023-10-06 23:25:11.657000

Syntax

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

Description

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

Arguments

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

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

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

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