NETWORKDAYS
Updated: 15 February 2010
Use NETWORKDAYS to calculate the number of whole working days between a start date and an end date. Working days exclude weekends (Saturday and Sunday) and dates identified as holidays.
Syntax
SELECT [wctString].[wct].[NETWORKDAYS] (
<@Holiday_TableName, nvarchar(4000),>
,<@HolidayDates_ColumnName, nvarchar(4000),>
,<@GroupedColumnName, nvarchar(4000),>
,<@GroupedColumnValue, sql_variant,>
,<@StartDate, datetime,>
,<@EndDate, datetime,>)
Arguments
@Holiday_TableName
the name of the TABLE or VIEW containing the holidays to be used by the function. The @Holiday_TableName argument can be of data types that are implicitly convertible to nvarchar or ntext.
@HolidayDates_ColumnName
the name of the column in the TABLE or VIEW containing the holidays to be used by the function. The data in the column must be of the datetime data type. The @HolidayDate_ColumnName argument can be of data types that are implicitly convertible to nvarchar or ntext.
@GroupedColumnName
the name of the column in the TABLE or VIEW containing the holidays to be used in grouping the results. If you do not want to specify a @GroupedColumnName , enter blank (''). The @GroupedColumnName argument can be of data types that are implicitly convertible to nvarchar or ntext.
@GroupedColumnValue
the value in @GroupedColumnName to be used in grouping the results. If no grouping is desired, enter NULL.
@StartDate
The starting date for the calculation. Must be of data types that are implicitly convertible to datetime.
@EndDate
The ending date for the calculation. Must be of data types that are implicitly convertible to datetime.
Return Types
float
Remarks
· If @StartDate > @Enddate, the result will be negative.
· The calendar may be omitted by entering '' in the @Holiday_TableName.
· Saturday and Sunday are assumed to be non-working days.
Examples
CREATE TABLE #t (
Holiday datetime
)
INSERT INTO #t
SELECT '1/1/2010' UNION ALL
SELECT '1/18/2010' UNION ALL
SELECT '2/15/2010' UNION ALL
SELECT '4/2/2010' UNION ALL
SELECT '5/31/2010' UNION ALL
SELECT '7/5/2010' UNION ALL
SELECT '9/6/2010' UNION ALL
SELECT '11/25/2010' UNION ALL
SELECT '12/24/2010'
SELECT wct.NETWORKDAYS('#t'
,'Holiday'
,''
,NULL
,'12/31/2009'
,'02/16/2010')
This produces the following result.
----------------------
31
(1 row(s) affected)
If you did not wish to specify a holiday calendar, it can be omitted from the calculation by entering blank ('') in @Holiday_TableName and @HolidayDates_ColumnName.
SELECT wct.NETWORKDAYS(''
,''
,''
,NULL
,'12/31/2009'
,'02/16/2010')
This produces the following result.
----------------------
34
(1 row(s) affected)
If the start date is less than the end date, the function returns a negative number.
CREATE TABLE #t (
Holiday datetime
)
INSERT INTO #t
SELECT '1/1/2010' UNION ALL
SELECT '1/18/2010' UNION ALL
SELECT '2/15/2010' UNION ALL
SELECT '4/2/2010' UNION ALL
SELECT '5/31/2010' UNION ALL
SELECT '7/5/2010' UNION ALL
SELECT '9/6/2010' UNION ALL
SELECT '11/25/2010' UNION ALL
SELECT '12/24/2010'
SELECT wct.NETWORKDAYS('#t'
,'Holiday'
,''
,NULL
,'02/16/2010'
,'12/31/2009')
This produces the following result.
----------------------
-31
(1 row(s) affected)
See Also