BUSINESSDATEWE
Updated: 11 May 2012
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.
Syntax
SELECT [wctFinancial].[wct].[BUSINESSDATEWE] (
<@StartDate, datetime,>
,<@DatePart, nvarchar(4000),>
,<@Number, int,>
,<@DateRollRule, nvarchar(4000),>
,<@Holidays, nvarchar(max),>
,<@WE1, int,>
,<@WE2, int,>)
Arguments
@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.
@DatePart
the part of @Startdate to which @Number is added. BUSINESSDATEWE 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.
@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.
@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.
Return Type
float
Remarks
· @DatePartmust be'D','d','W','w','M','m','Y', or 'y'
· @DatePartmust 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
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
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.
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
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.
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.EOMONTH(GETDATE(),0) --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).
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).
Business Date
-----------------------
2012-12-31 00:00:00.000
See Also