SQL Server WORKDAY_q Function

Updated 2023-11-09 21:17:46.913000

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.

Syntax

SELECT [westclintech].[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 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"}]}