Login     Register

        Contact Us     Search

XLeratorDB/strings Online Documentation

SQL Server NETWORKDAYS function


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
·         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.
----------------------
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
·         NBD - Create holiday string


Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service