Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server number of business days between 2 dates


BUSDAYSWE

Updated: 05 May 2014


Use the scalar function BUSDAYSWE to calculate the number of business days from a start date (inclusive) to an end date (exclusive), where the weekend days are not Saturday and Sunday. BUSDAYSWE lets you specify one or two consecutive days of the week as weekend days.
Syntax
SELECT [wctFinancial].[wct].[BUSDAYSWE](
  <@StartDate, datetime,>
 ,<@EndDate, datetime,>
 ,<@Holidays, nvarchar(max),>
 ,<@WE1, int,>
 ,<@WE2, int,>)
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 to create an appropriately formatted string.
@WE1
the first weekend day. Sunday is day 0; Saturday is day 6. @WE1 is an expression of type int or of a type that can be implicitly converted to int.
@WE2
the second weekend day. Sunday is day 0; Saturday is day 6. @WE2 is an expression of type int or of a type that can be implicitly converted to int.
Return Type
int
Remarks
·         @StartDate cannot be NULL
·         @EndDate cannot be NULL
·         If @WE1 IS NULL and @WE2 IS NULL then @WE1 = 6 and @WE2 = 0
·         If @WE2 is NULL and @WE1 is NOT NULL then @WE2 = @WE1
·         If @WE1 is NULL and @WE2 is NOT NULL then @WE1 = @WE2
·         @WE1 must be between 0 and 6
·         @WE2 must be between 0 and 6
Examples
In this example we calculate the number of business days from Wednesday to Wednesday assuming that the only weekend day is Sunday.
SELECT
   wct.BUSDAYSWE(
      '2014-05-01',     --@Startdate     
      '2014-05-08',     --@Enddate
      NULL,             --@Holidays
      0,                --@WE1
      NULL              --@WE2
      ) as [Business Days]
This produces the following result.
Business Days
-------------
            6


In this example, we add some holidays, and we calculate the number number of business days in the month of May, assuming that the only weekend day is Sunday.
DECLARE @h as varchar(max)
SET @h =(
   SELECT
      wct.NBD(d)
   FROM (
      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'
      ) n(d)
   )
 
SELECT
   wct.BUSDAYSWE(
      '2014-05-01',     --@Startdate     
      '2014-06-01',     --@Enddate
      @h,               --@Holidays
      0,                --@WE1
      NULL             --@WE2
      ) as [Business Days]
This produces the following result.
Business Days
-------------
           26


In this example, we calculate the number of business days for each month in 2014 assuming that the only weekend day is Sunday.
DECLARE @h as varchar(max)
SET @h =(
   SELECT
      wct.NBD(d)
   FROM (
      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'
      ) n(d)
   )
  
SELECT
   DATENAME(m,StartDate) as [Month],
   wct.BUSDAYSWE(StartDate,enddate,@h,0,NULL) as [Business Days]
FROM (
   SELECT
      wct.CALCDATE(2014,m,1) as StartDate,
      wct.EDATE(wct.CALCDATE(2014,m,1),1) as EndDate
   FROM (
      VALUES
      (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)
      )n(m)
   )p

This produces the following result.
Month                          Business Days
------------------------------ -------------
January                                   25
February                                  23
March                                     26
April                                     26
May                                       26
June                                      25
July                                      26
August                                    26
September                                 25
October                                   26
November                                  23
December                                  26


In this example, the weekend days are Monday and Tuesday.
DECLARE @h as varchar(max)
SET @h =(
   SELECT
      wct.NBD(d)
   FROM (
      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'
      ) n(d)
   )
  
SELECT
   DATENAME(m,StartDate) as [Month],
   wct.BUSDAYSWE(StartDate,enddate,@h,1,2) as [Business Days]
FROM (
   SELECT
      wct.CALCDATE(2014,m,1) as StartDate,
      wct.EDATE(wct.CALCDATE(2014,m,1),1) as EndDate
   FROM (
      VALUES
      (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)
      )n(m)
   )p
This produces the following result.
Month                          Business Days
------------------------------ -------------
January                                   22
February                                  20
March                                     22
April                                     21
May                                       23
June                                      21
July                                      21
August                                    23
September                                 20
October                                   23
November                                  21
December                                  20
 


Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service