Login     Register

        Contact Us     Search

XLeratorDB/strings Online Documentation

SQL Server NETWORKDAYS function


NETWORKDAYS_q
 
Updated: 15 February 2010

Use NETWORKDAYS_q 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_q] (
  <@StartDate, datetime,>
 ,<@EndDate, datetime,>
 ,<@HolidayDates_RangeQuery, nvarchar(max),>)
Arguments
@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.
 
@HolidayDates_RangeQuery
the select statement, as text, which will return a resultant table containing the holiday dates. The resultant table can only contain values of a datetime date type or that will implicitly convert to datetime.
Return Types
float
Remarks
·         For simpler queries, use NETWORKDAYS.
·         If @StartDate > @Enddate, the result will be negative.
·         The calendar may be omitted by entering '' in the @HolidayDates_RangeQuery.
·         Saturday and Sunday as assumed to be non-working days.
Examples
SELECT wct.NETWORKDAYS_q(
      '12/31/2009'
      ,'02/16/2010'
      ,'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'''
      )
 
This produces the following result.

----------------------
31
 
(1 row(s) affected)

Or, if the holidays were stored in a table:
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_q(
      '12/31/2009'
      ,'02/16/2010'
      ,'SELECT * FROM #t'
      )
 
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 @HolidayDates_RangeQuery.
SELECT wct.NETWORKDAYS_q(
      '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.
SELECT wct.NETWORKDAYS_q(
      '02/16/2010'
      ,'12/31/2009'
      ,REPLACE('SELECT ''1/1/2010''
      ''1/18/2010''
      ''2/15/2010''
      ''4/2/2010''
      ''5/31/2010''
      ''7/5/2010''
      ''9/6/2010''
      ''11/25/2010''
''12/24/2010''', CHAR(13) + CHAR(10), ' UNION ALL' + CHAR(10) + 'SELECT ')
      )

This produces the following result.
----------------------
-31
 

(1 row(s) affected)



Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service