WORKDAY_q
Updated 2023-11-09 21:17:46.913000
Syntax
SELECT [westclintech].[wct].[WORKDAY_q] (
<@StartDate, datetime,>
,<@Days, int,>
,<@HolidayDates_RangeQuery, nvarchar(max),>)
Description
Use the scalar function 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.
Arguments
@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.
@StartDate
The starting date for the calculation. Must be of data types that are implicitly convertible to datetime.
Return Type
datetime
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.
{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"2010-01-04 00:00:00.000"}]}
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.
{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"2010-01-01 00:00:00.000"}]}
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');
This produces the following result.
-----------------------
2010-02-16 00:00:00.000
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.
{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"2009-12-31 00:00:00.000"}]}