Logo

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"}]}