BUSINESSDATEWE
Updated 2023-10-06 23:35:47.560000
Syntax
SELECT [westclintech].[wct].[BUSINESSDATEWE] (
<@StartDate, datetime,>
,<@DatePart, nvarchar(4000),>
,<@Number, int,>
,<@DateRollRule, nvarchar(4000),>
,<@Holidays, nvarchar(max),>
,<@WE1, int,>
,<@WE2, int,>)
Description
Use the scalar function BUSINESSDATEWE 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. BUSINESSDATEWE allows you to specify the weekend day(s) to be used in the calculation. If your weekend days are always going to be Saturday and Sunday, you can use the BUSINESSDATE 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. BUSINESSDATEWE accepts days, weeks, months, or years in @DatePart.
@WE1
the first weekend day. Sunday is day 0; Saturday is day 6. @WE1 is an expression of type int or of a type that can be implicitly converted to int.
@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."}]}
@WE2
the second weekend day. Sunday is day 0; Saturday is day 6. @WE2 is an expression of type int or of a type that can be implicitly converted to int.
@StartDate
the date to be manipulated in this function. BUSINESSDATEWE 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 NBD 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'.
If @WE1 IS NULL and @WE2 IS NULL then @WE1 = 6 and @WE2 = 0.
If @WE2 is NULL and @WE1 is NOT NULL then @WE2 = @WE1.
If @WE1 is NULL and @WE2 is NOT NULL then @WE1 = @WE2.
@WE1 must be between 0 and 6.
@WE2 must be between 0 and 6.
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.BUSINESSDATEWE( '2012-05-04', --Start Date
'D', --Date Part
2, --Number
NULL, --Date Roll Rule
wct.NBD(Holiday), --Holidays
NULL, --Weekend Day 1
NULL --Weekend Day 2
) 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"}]}
What if we want to count Saturday as a business day?
SELECT wct.BUSINESSDATEWE( '2012-05-04', --Start Date
'D', --Date Part
2, --Number
NULL, --Date Roll Rule
wct.NBD(Holiday), --Holidays
6, --Weekend Day 1
NULL --Weekend Day 2
) 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-07 00:00:00.000"}]}
Let’s calculate 5 business days from 2012-05-21.
SELECT wct.BUSINESSDATEWE( '2012-05-21', --Start Date
'D', --Date Part
5, --Number
NULL, --Date Roll Rule
wct.NBD(Holiday), --Holidays
NULL, --Weekend Day 1
NULL --Weekend Day 2
) 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.BUSINESSDATEWE( '2012-02-28', --Start Date
'M', --Date Part
3, --Number
NULL, --Date Roll Rule
wct.NBD(Holiday), --Holidays
NULL, --Weekend Day 1
NULL --Weekend Day 2
) 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 BUSINESSDATEWE 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.BUSINESSDATEWE(wctFinancial.wct.CALCDATE(YEAR(GETDATE()), 12, 31), --Start Date
'M', --Date Part
0, --Number
'P', --Date Roll Rule
wct.NBD(Holiday), --Holidays
NULL, --Weekend Day 1
NULL --Weekend Day 2
) 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.BUSINESSDATEWE(wctFinancial.wct.CALCDATE(YEAR(GETDATE()), 12, 31), --Start Date
'M', --Date Part
0, --Number
'P', --Date Roll Rule
wct.NBD(Holiday), --Holidays
NULL, --Weekend Day 1
NULL --Weekend Day 2
) 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
BUSINESSDATE - Calculate a Business Date from an offset
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