BUSINESSDATE
Updated 2024-02-13 20:47:07.873000
Syntax
SELECT [westclintech].[wct].[BUSINESSDATE] (
<@StartDate, datetime,>
,<@DatePart, nvarchar(4000),>
,<@Number, int,>
,<@DateRollRule, nvarchar(4000),>
,<@Holidays, nvarchar(max),>)
Description
Use the scalar function BUSINESSDATE to return a specified date with the specified number interval (which is a signed integer) added to a specified date part of the specified date. When the specified date is 'D' (for days), the function will add the number interval and exclude holidays and weekend days in the calculation. BUSINESSDATE always uses Saturday and Sunday as weekend days. If you want to supply your own weekend days, use the BUSINESSDATEWE function.
When the date part is not 'D', the calculated date may be adjusted to the next or previous business date based upon the entered date roll convention.
Arguments
@DatePart
the part of @Startdate to which @Number is added. BUSINESSDATE accepts days, weeks, months, or years in @DatePart.
@Number
an integer that is added to the @DatePart of @StartDate. @Number is an expression of type int or of a type that can be implicitly converted to int.
@DateRollRule
Identifies the date rolling convention to be used when the result falls on a non-business day and the @DatePart is week, month, or year. The @DateRollRule values are:
{"columns":[{"field":"A","width":67},{"field":"actual day is returned with no adjustment.","width":523}],"rows":[{"A":"F","actual day is returned with no adjustment.":"next business day is returned."},{"A":"M","actual day is returned with no adjustment.":"next business day is returned unless it is in a different month in which case the previous business day is returned."},{"A":"P","actual day is returned with no adjustment.":"preceding business day is returned."},{"A":"MP","actual day is returned with no adjustment.":"preceding business day is returned unless it is in a different month in which in case the next business day is returned."}]}
@StartDate
the date to be manipulated in this function. BUSINESSDATE will add the specified @number and @Datepart and return a result. @StartDate is an expression of type datetime or of a type that can be implicitly converted to datetime.
@Holidays
a comma separated string containing the holiday (non-business) dates to be used in the calculation of the number of business days. You can use the aggregate function NDB to create an appropriately formatted string.
Return Type
datetime
Remarks
@DatePart must be 'D', 'd', 'W', 'w', 'M', 'm', 'Y' or 'y'.
@DateRollRule must be 'A', 'a', 'F', 'f', 'M', 'm', 'P', 'p', 'MP' or 'mp'.
If @DateRollRule is NULL, it is set to 'F'.
Examples
For Example purposes, we have put the US holiday calendar into a table called HOLIDAYS which is keyed by country code and the holiday date.
CREATE TABLE HOLIDAYS
(
COUNTRY CHAR(2) NOT NULL,
HOLIDAY datetime NOT NULL,
PRIMARY KEY (
COUNTRY,
HOLIDAY
)
);
INSERT INTO HOLIDAYS
VALUES
('US', '2012-01-02');
INSERT INTO HOLIDAYS
VALUES
('US', '2012-01-16');
INSERT INTO HOLIDAYS
VALUES
('US', '2012-02-20');
INSERT INTO HOLIDAYS
VALUES
('US', '2012-05-28');
INSERT INTO HOLIDAYS
VALUES
('US', '2012-07-04');
INSERT INTO HOLIDAYS
VALUES
('US', '2012-09-03');
INSERT INTO HOLIDAYS
VALUES
('US', '2012-10-08');
INSERT INTO HOLIDAYS
VALUES
('US', '2012-11-12');
INSERT INTO HOLIDAYS
VALUES
('US', '2012-11-22');
INSERT INTO HOLIDAYS
VALUES
('US', '2012-12-25');
In this example, we calculate 2 business days from 2012-05-04.
SELECT wct.BUSINESSDATE( '2012-05-04', --Start Date
'D', --Date Part
2, --Number
NULL, --Date Roll Rule
wct.NBD(Holiday) --Holidays
) as [Business Date]
FROM HOLIDAYS
WHERE Country = 'US';
This produces the following result.
{"columns":[{"field":"Business Date","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Business Date":"2012-05-08 00:00:00.000"}]}
Let’s calculate 5 business days from 2012-05-21.
SELECT wct.BUSINESSDATE( '2012-05-21', --Start Date
'D', --Date Part
5, --Number
NULL, --Date Roll Rule
wct.NBD(Holiday) --Holidays
) as [Business Date]
FROM HOLIDAYS
WHERE Country = 'US';
This produces the following result.
{"columns":[{"field":"Business Date","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Business Date":"2012-05-29 00:00:00.000"}]}
Let’s Calculate 3 months from 2012-02-28.
SELECT wct.BUSINESSDATE( '2012-02-28', --Start Date
'M', --Date Part
3, --Number
NULL, --Date Roll Rule
wct.NBD(Holiday) --Holidays
) as [Business Date]
FROM HOLIDAYS
WHERE Country = 'US';
This produces the following result.
{"columns":[{"field":"Business Date","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Business Date":"2012-05-29 00:00:00.000"}]}
The date was advanced to 2012-05-29 because @DateRollRule is NULL and we used the default of F, rolling the date forward to the next business date.
You can use BUSINESSDATE in conjunction with other XLeratorDB date functions. For example, let’s say you wanted to get the last business date of the current month. We could enter the following SQL statement:
SELECT wct.BUSINESSDATE( wctFinancial.wct.EOMONTH(GETDATE(), 0), --Start Date
'M', --Date Part
0, --Number
'P', --Date Roll Rule
wct.NBD(Holiday) --Holidays
) as [Business Date]
FROM HOLIDAYS
WHERE Country = 'US';
This returns the following result (your result may be different).
{"columns":[{"field":"Business Date","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Business Date":"2012-05-31 00:00:00.000"}]}
This statement will return the last business date of the current year.
SELECT wct.BUSINESSDATE( wctFinancial.wct.CALCDATE(YEAR(GETDATE()), 12, 31),
--Start Date
'M',
--Date Part
0,
--Number
'P',
--Date Roll Rule
wct.NBD(Holiday)
--Holidays
) as [Business Date]
FROM HOLIDAYS
WHERE Country = 'US';
This produces the following result (your result may be different).
{"columns":[{"field":"Business Date","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Business Date":"2012-12-31 00:00:00.000"}]}
See Also
BUSDAYS - Calculate the number of business days between two dates
BUSINESSDATEWE - Calculate a Business Date from an offset and specified weekend days
FIRSTWEEKDAY - Calculate the last occurrence of a weekday in a given month
LASTWEEKDAY - Calculate the first occurrence of a weekday in a given month
NETWORKDAYS - Calculate the number of working days between two dates (inclusive)
YEARFRAC - Calculate the difference between two dates as a fraction of a year