Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server number of business days between 2 dates


BUSDAYS

Updated: 11 May 2012


Use the scalar function BUSDAYS to calculate the number of business days from a start date (inclusive) to an end date (exclusive).
Syntax
SELECT [wctFinancial].[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 NDB 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.
Start Date End Date   Business Days      Actual
---------- ---------- ------------- -----------
2015-08-01 2014-11-19          -176        -255
2014-11-01 2015-04-12           109         162
2010-03-16 2015-12-12          1444        2097
2009-10-21 2011-11-24           529         764
2014-07-22 2015-10-13           308         448
2013-02-03 2012-02-27          -236        -342
2013-04-26 2015-01-23           436         637
2013-01-07 2011-06-07          -397        -580
2015-08-04 2010-09-23         -1220       -1776
2013-12-16 2015-08-25           425         617
2015-01-02 2013-08-07          -351        -513
2009-12-02 2011-10-07           467         674
2014-06-14 2015-10-23           341         496
2010-01-15 2015-03-13          1294        1883
2015-01-19 2010-06-07         -1159       -1687
2014-09-20 2012-12-31          -434        -628
2009-11-08 2014-09-01          1211        1758
2011-07-28 2015-09-24          1044        1519
2010-11-07 2011-06-08           146         213
2013-01-08 2014-11-26           474         687
2012-07-12 2015-02-04           642         937
2012-07-03 2012-08-30            41          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
Start Date End Date   Business Days      Actual
---------- ---------- ------------- -----------
2015-08-01 2014-11-19          -175        -255
2014-11-01 2015-04-12           108         162
2010-03-16 2015-12-12          1443        2097
2009-10-21 2011-11-24           528         764
2014-07-22 2015-10-13           308         448
2013-02-03 2012-02-27          -235        -342
2013-04-26 2015-01-23           436         637
2013-01-07 2011-06-07          -397        -580
2015-08-04 2010-09-23         -1220       -1776
2013-12-16 2015-08-25           425         617
2015-01-02 2013-08-07          -351        -513
2009-12-02 2011-10-07           467         674
2014-06-14 2015-10-23           341         496
2010-01-15 2015-03-13          1294        1883
2015-01-19 2010-06-07         -1158       -1687
2014-09-20 2012-12-31          -433        -628
2009-11-08 2014-09-01          1210        1758
2011-07-28 2015-09-24          1044        1519
2010-11-07 2011-06-08           146         213
2013-01-08 2014-11-26           474         687
2012-07-12 2015-02-04           642         937
2012-07-03 2012-08-30            41          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.
Start Date End Date    Business Days Actual Days
----------- ----------- ------------- -----------
26 May 2012 26 May 2012             0           0
26 May 2012 27 May 2012             0           1
26 May 2012 28 May 2012             0           2
26 May 2012 29 May 2012             0           3
26 May 2012 30 May 2012             1           4
26 May 2012 31 May 2012             2           5
26 May 2012 01 Jun 2012             3           6
26 May 2012 02 Jun 2012             3           7
26 May 2012 03 Jun 2012             3           8
26 May 2012 04 Jun 2012             4           9
26 May 2012 05 Jun 2012             5          10
26 May 2012 06 Jun 2012             6          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.
Start Date End Date    Business Days Actual Days
----------- ----------- ------------- -----------
23 May 2012 03 Jun 2012             6          11
24 May 2012 03 Jun 2012             5          10
25 May 2012 03 Jun 2012             4           9
26 May 2012 03 Jun 2012             3           8
27 May 2012 03 Jun 2012             3           7
28 May 2012 03 Jun 2012             3           6
29 May 2012 03 Jun 2012             3           5
30 May 2012 03 Jun 2012             2           4
31 May 2012 03 Jun 2012             1           3
01 Jun 2012 03 Jun 2012             0           2
02 Jun 2012 03 Jun 2012             0           1
03 Jun 2012 03 Jun 2012             0           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
·         NBD - Create holiday string


Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service