BUSINESSDATE
Updated: 11 May 2012
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.
Syntax
SELECT [wctFinancial].[wct].[BUSINESSDATE] (
<@StartDate, datetime,>
,<@DatePart, nvarchar(4000),>
,<@Number, int,>
,<@DateRollRule, nvarchar(4000),>
,<@Holidays, nvarchar(max),>)
Arguments
@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.
@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:
|
A
|
actual day is returned with no adjustment.
|
|
F
|
next business day is returned.
|
|
M
|
next business day is returned unless it is in a different month in which case the previous business day is returned.
|
|
P
|
preceding business day is returned.
|
|
MP
|
preceding business day is returned unless it is in a different month in which in case the next business day is returned.
|
@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
float
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
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
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
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.
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).
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).
Business Date
-----------------------
2012-12-31 00:00:00.000
See Also