WORKDAY_q
Updated: 15 February 2010
Use WORKDAY_q to calculate the date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. The weekend days are always assumed to be Saturday and Sunday.
Syntax
SELECT [wctString].[wct].[WORKDAY_q] (
<@StartDate, datetime,>
,<@Days, int,>
,<@HolidayDates_RangeQuery, nvarchar(max),>)
Arguments
@StartDate
The starting date for the calculation. Must be of data types that are implicitly convertible to datetime.
@Days
is the number of non-weekend and non-holiday days before or after the @StartDate. A positive value for @Days yields a future date; a negative value yields a date in the past.
@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 more simpler queries, use WORKDAY.
· The calendar may be omitted by entering '' in the @Holiday_TableName.
· Saturday and Sunday are assumed to be non-working days.
· If @Days is not an integer, it is truncated.
Examples
In this example, we want to calculate the next workday after 12/31/2009.
SELECT wct.WORKDAY_q(
'12/31/2009'
,1
,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.
-----------------------
2010-01-04 00:00:00.000
(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.WORKDAY_q(
'12/31/2009'
,1
,''
)
This produces the following result.
-----------------------
2010-01-01 00:00:00.000
(1 row(s) affected)
If the holidays are 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.WORKDAY_q(
'12/31/2009'
,30
,'SELECT * FROM #t'
)
If @Days is a negative number, the result will be less than the start date
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.WORKDAY_q(
'2/16/2010'
,-30
,'SELECT * FROM #t'
)
This produces the following result.
-----------------------
2009-12-31 00:00:00.000
(1 row(s) affected)