WORKDAY
Updated: 15 February 2010
Use WORKDAY 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] (
<@Holiday_TableName, nvarchar(4000),>
,<@HolidayDates_ColumnName, nvarchar(4000),>
,<@GroupedColumnName, nvarchar(4000),>
,<@GroupedColumnValue, sql_variant,>
,<@StartDate, datetime,>
,<@Days, int,>)
Arguments
@Holiday_TableName
the name of the TABLE or VIEW containing the holidays to be used by the function. The @Holiday_TableName argument can be of data types that are implicitly convertible to nvarchar or ntext.
@HolidayDates_ColumnName
the name of the column in the TABLE or VIEW containing the holidays to be used by the function. The data in the column must be of the datetime data type. The @HolidayDate_ColumnName argument can be of data types that are implicitly convertible to nvarchar or ntext.
@GroupedColumnName
the name of the column in the TABLE or VIEW containing the holidays to be used in grouping the results. If you do not want to specify a @GroupedColumnName , enter blank (''). The @GroupedColumnName argument can be of data types that are implicitly convertible to nvarchar or ntext.
@GroupedColumnValue
the value in @GroupedColumnName to be used in grouping the results. If no grouping is desired, enter NULL.
@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.
Return Types
float
Remarks
· For more complicated queries, use WORKDAY_q.
· 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.
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('#t'
,'Holiday'
,''
,NULL
,'12/31/2009'
,1)
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(''
,''
,''
,NULL
,'12/31/2009'
,1)
This produces the following result.
-----------------------
2010-01-01 00:00:00.000
(1 row(s) affected)
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('#t'
,'Holiday'
,''
,NULL
,'2/16/2010'
,-29)
This produces the following result.
-----------------------
2010-01-04 00:00:00.000
(1 row(s) affected)