NETWORKDAYS

Updated 2023-11-09 20:56:56.417000

Syntax

SELECT [westclintech].[wct].[NETWORKDAYS] (
  <@Holiday_TableName, nvarchar(4000),>
 ,<@HolidayDates_ColumnName, nvarchar(4000),>
 ,<@GroupedColumnName, nvarchar(4000),>
 ,<@GroupedColumnValue, sql_variant,>
 ,<@StartDate, datetime,>
 ,<@EndDate, datetime,>)

Description

Use the scalar function 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.

Arguments

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

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

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

@EndDate

The ending date for the calculation. Must be of data types that are implicitly convertible to datetime.

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

Return Type

float

Remarks

For more complicated queries, use NETWORKDAYS_q.

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.

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"31"}]}

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.

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"34"}]}

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.

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"-31"}]}

See Also

BUSDAYS - Calculate the number of business days between two dates

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

NBD - Create holiday string

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