NBD
Updated: 11 May 2012
Use the aggregate function NBD to create a comma separated string of non-business dates in the format YYYYMMDD. This string is used in various programs to store holiday information used in date calculations.
Syntax
SELECT [westclintech].[wct].[NBD] (
<@Holiday, datetime,>)
Arguments
@Holiday
the holiday or non-business day to be included in the concatenated result. @Holiday is an expression of type datetime or of a type that can be implicitly converted to datetime.
Return Type
nvarchar(max)
Remarks
· SQL Server 2005 users need to limit the length of the concatenated string to 8,000 bytes. The length of the string is the (number of holidays * 9) – 1, implying a limit of 889 non-business days in the string.
· Weekend days do not need to be specified as non-business days.
Example
Let’s say we had a table called HOLIDAYS which stores holidays by COUNTRY. We could create the appropriate string, containing all the US holidays in 2012 with the following SELECT.
SELECT wct.NBD(holiday) as NBD
FROM HOLIDAYS
WHERE COUNTRY = 'US' and YEAR(Holiday) = 2010
This produces the following result.
NBD
-----------------------------------------------------------------------------------------
20100101,20100118,20100215,20100531,20100705,20100906,20101011,20101125,20101224,20101231
Normally, there will be no need to return the NBD string. It can just be passed into functions as required. In this example, we will create a string of US holidays and then calculate the number of business days betwee 2021-05-15 and 2012-07-15.
SELECT wct.BUSDAYS(
'2012-05-15' --Start Date
,'2012-07-15' --End Date
,wct.NBD(holiday) --Non-business Days
) as BUSDAYS
FROM HOLIDAYS
WHERE COUNTRY = 'US'
This produces the following result.
BUSDAYS
-----------
41
See Also