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
· 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)